Biggsy Biggsy - 13 days ago 5
MySQL Question

MySQL: Count event attendance based on event type

I have three tables set up to track attendance at events. The tables are as follows:

Users
--------
rid
name

Events
--------
eid
name
type (either 0 or 1)

Attendance
--------
rid
eid


I'm trying to return all of the users and print out a count of the amount of events they've attended broken into the amount for type 1 and type 0.

I can successfully return one or the other but not a combination of both with the following statement:

SELECT users.name, COUNT(type) as typeCount FROM attendance, events, users
WHERE users.rid=attendance.rid && events.eid=attendance.eid && type=1 GROUP BY users.rid


Which returns

╔══════╦══════════════╗
║ name ║ typeCount ║
╠══════╬══════════════╣
║ John ║ 5 ║
╠══════╬══════════════╣
║ Jack ║ 3 ║
╚══════╩══════════════╝


But I would like it to return

╔══════╦══════════════╦═════════════╗
║ name ║ type0Count ║ type1Count ║
╠══════╬══════════════╬═════════════╣
║ John ║ 2 ║ 5 ║
╠══════╬══════════════╬═════════════╣
║ Jack ║ 7 ║ 3 ║
╚══════╩══════════════╩═════════════╝

Answer

You can use CASE...WHEN inside the COUNT function to count specific values in a column.

SELECT
    Users.rid,
    Users.Name,
    COUNT(CASE Events.type WHEN 0 THEN 1 ELSE NULL END) AS type0Count,
    COUNT(CASE Events.type WHEN 1 THEN 1 ELSE NULL END) AS type1Count
FROM Users
INNER JOIN Attendance ON Users.rid = Attendance.rid
INNER JOIN Events ON Attendance.eid = Events.eid
GROUP BY Users.rid, Users.Name

You can also use IF or NULLIF as well.