mardi 28 juillet 2020

Count of occurrence of a string group by day in Google spreadsheet

I have table data in Google Spreadsheet something like this:

Date|Diet
4-Jan-2020|Coffee
4-Jan-2020|Snacks
4-Jan-2020|xyz
4-Jan-2020|Coffee
5-Jan-2020|Snacks
5-Jan-2020|abc
6-Jan-2020|Coffee
6-Jan-2020|Snacks

This table is a list of food items I had on a daily basis. I would like to get the number of times I had coffee on a daily basis. So I would like to get the output like this:

Date | No of times I had Coffee    
4-Jan-2020| 2
5-Jan-2020| 0
6-Jan-2020| 1

I used this query to get the output.

=query(A1:B1425,"select A, COUNT(B) where B='Coffee' group by A")

With this query, I get the below output. Do note that I don't get those days when I didn't have coffee

4-Jan-2020| 2
6-Jan-2020| 1

So count for 5-Jan-2020 is missing because there is no string "Coffee" for that day.

How do I get the desired output including the count 0? Thank you.

Aucun commentaire:

Enregistrer un commentaire