CODEWITHSUNDEEP
sql
Duplosion
Duplosion

Reputation: 269

Counting events by unique ID

I have some downtime and I'm spending it trying to learn SQL. I've run into a question that I'm hoping you can help me with.

Let's say I have an incredibly simple table like this, that has a unique ID for each date (again, this is a learning exercise -- otherwise I'd use an actual date and not an ID), followed by the chores I did that day:

day_id  | chore

123456  | Washed the dishes

823454  | Cut the grass

123456  | Washed the dishes

123456  | Took out the trash

324234  | Washed the dishes

As you can see, it's possible that you could do the same chore twice on the same day (in this case, I washed the dishes twice on 123456).

How could I get a count of the unique number of days that I washed the dishes (in this case, the answer should be 2)?

Upvotes: 0

Views: 79

Answers (3)

rs.
rs.

Reputation: 27427

SELECT CHORE, COUNT(DISTINCT day_id) cnt
FROM CHORES
WHERE CHORE='Washed the dishes' -- remove this to get list of all chores/count
GROUP BY CHORE

Upvotes: 2

juergen d
juergen d

Reputation: 204746

select count(distinct day_id) 
from your_table
where chore = 'Washed the dishes'

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 452977

SELECT COUNT(DISTINCT day_id)
FROM Chores
WHERE Chore='Washed the dishes'

Upvotes: 3

Related Questions