SQL Count and countif in single Query

HI Team,

I am just trying to learn the SQL and have one question.
Please have a look on the attached pic and help me to get the result from the data by using SQL query.

Query need to count the names and then comments based on condition.

Ex : in attached pic have two names repeated 5 times so the count of each name is 5 and
for 1st name have 5 comments as "Yes" so count should be 5, for other name in comment we have "Yes" 4 time so result should be 4 (for comments condition value is "Yes")

Please note: i am unable to attach the sample file as the "Attachments" option is not working.

Thanks in advance for all your help and support.

Access data.png

Hi Alansidman,

Thank you so much for attending my request.

As advised, attached the sample file for your reference.

Kindly have a look and advice how to achieve my goal in Access SQL.

I tried the links provided in the post but failed to achieve the result.

Kindly advise and thanks in advance for all your help and support.

Regards,
Rahaman.

Hi Alansidman,

Apologies for the inconvenience caused.

Here is the attachment.

Regards,
Rahaman.

Hi Alansidman,

I am already aware of what you are advising but i am not able to achieve the result what i am expecting.

my question is simple
1. first Just count the names
2 count the comments where it contains word "Yes"

Expected result : Names (ABC) count is 5 and comment where word "Yes" contain is also 5
Names (XYZ) count is 5 and comment where word "Yes" contains count is 4.

Hope i am clear with my question.

Kindly advise.

Thanks in advance for all your help and support.

Regards,
Rahaman.

Hi Alansidman,

Let me try one more time....

Could you please have a look on excel attachment where i have created 3 sheets.
1. First sheet refers to raw data
2. Second and third sheets contains the result using formula.
( I just used counta and countif formula to get the output/result.)

The same thing want to achieve using sql query in access database/table.

Please note : To avoid confusion i have deleted the existing queries in access file.

if it is still not clear then kindly close the thread, will come back with better explanation after sometimes.

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

Have a great day and Happy New year too.

Regards,
Rahaman.

Hi Team,

Here is the solution offered by www.accessforums.net and its working fine.

Please Login or Register  to view this content.

Kindly note: thread was not showing in www.accessforums.net to provide the link and it was pending with the moderator for approval.
and here is the link
https://www.accessforums.net/showthread.php?t=82580

Kindly close the thread.

Thank you so much for all your help and support.

Regards,
Rahaman.

Hi Alansidman,

Thank you so much for all your help and support.

Regards,
Rahaman.