CODEWITHSUNDEEP
mysqlsqldatetime
user979331
user979331

Reputation: 11871

Query to get a count of items including zero for missing data

I have this table called rsvp which has 3 columns:

The time will either be 6:00pm, 6:30pm, 7:00pm, 7:30pm

What I am trying to do is get a query that will return 2 columns. Each of the times and the number of times these times are used.

For example, if I have two items in my table. One had a time of 6:00pm and the other is 7:00pm

My query would return:

6:00pm - 1
6:30pm - 0
7:00pm - 1
7:30pm - 0

How would I get these columns from a mysql query?

Upvotes: 1

Views: 49

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

Here is one way to do this:

SELECT timelist.time, COUNT(rsvp.id) FROM (
    SELECT '6:00pm' AS time UNION ALL
    SELECT '6:30pm'         UNION ALL
    SELECT '7:00pm'         UNION ALL
    SELECT '7:30pm'
) AS timelist
LEFT JOIN rsvp ON timelist.time = rsvp.time
GROUP BY timelist.time
ORDER BY timelist.time

Upvotes: 3

ajtrichards
ajtrichards

Reputation: 30565

SELECT COUNT(id) as count, time FROM rsvp GROUP BY time

Upvotes: 2

Related Questions