My database has a layout like this:
| StudentID | ActivityNumber | Payed |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 0 |
| 3 | 1 | 1 |
| 4 | 2 | 0 |
The Payed column is a boolean to look if a perseon has payed yes or no and not to look how much he/she payed now as you see nobody payed for activity 2 but if i use this command
SELECT ActivityNumber, COUNT(*) as TimesPayed
FROM Registration
WHERE Payed = 1
GROUP BY ActivityNumber
MySQL doesn't show ActivityNumber 2 in the results because the count is 0 but I want to show that the count is 0 and I don't know how because if i delete the WHERE clause it will count it as 1 and not 0
The result i expect is this:
| ActivityNumber | TimesPayed |
|---|---|
| 1 | 2 |
| 2 | 0 |
CodePudding user response：
Use conditional SUM.
Query -
SELECT ActivityNumber, sum( case when payed = 1 then 1 else 0 end ) as TimesPayed
FROM Registration
group by ActivityNumber