SQL Count and countif in single Query

[naseerrahaman]

HI Team,




I am just trying to learn the SQL and have one question.

In attached access file have single table where it contains fields(1) names and fields(2) comments.

1. name (ABC) repeated 5 times so the count of names should be 5 and in comments word "Yes" repeated 5 time for name abc count of comment should also be 5
2. name (XYZ) repeated 5 times so the count of names should be 5 and in comments word "Yes" repeated 4 time for name xyz count of comment should be 4

i am trying to achieve the above result using access query and for better understanding please have a look on the attached excel file.

Excel file : 1st sheet contains raw data
2 and 3 sheet contains the result/output using the counta and countif formula.

I request you to kindly advise how to achieve the expected result in access using query.

Please Note : Cross posted in below site as well.

link : https://www.excelforum.com/access-ta...ml#post5448050


Thanks in advance for all your help and support.

[Welshgasman]

This could be one way

SELECT Sheet1.Names, Sheet1.Names, Count(Sheet1.Names) AS CountOfNames, Sum(IIf([Comments]='Yes',1,0)) AS Expr1
FROM Sheet1
GROUP BY Sheet1.Names, Sheet1.Names;

And a Big thank you for notifying of the cross posting.

[naseerrahaman]

Hi Welshgasman,

Thank you so much for attending my request and solution offered is working fine as expected.

Once again thank you so much for all your help and support.

Regards,
Rahaman.

[CJ_London]

you don't even need the iif statement since booleans return -1 or 0 (the answer you get to a comparison), -1=true, 0=false

-Sum([Comments]='Yes')

or

sum(-([Comments]='Yes'))

[naseerrahaman]

HI Ajax,

Yes, solution is working fine.

Thank you so much for all your help and support.

Regards,
Rahaman