In a previous blog, I mentioned that a friend in the UK had a problem with getting some SQL code to do what he wanted. Over MSN, he sent me a script to build the 3 tables involved, an “ERD” type diagram, and a description of the problem.
It went like this… He had 3 tables: Events, Users and Bookings.
Events has an EventID, and Name
Users has a UserID, a Name, and a Gender (bit field – 0 = female, 1=male)
Bookings has an EventID, and a UserID
He needed to be able to, in one statement, get a count of how many males, and how many females were attending all of the events. He’d tried to use “Group By” and “Having” to get the right information, but the solution I gave him was as follows:
SELECT
Events.EventID,
SUM(CASE Users.Gender WHEN 0 THEN 0 WHEN 1 THEN 1 END) AS ‘NumMales’,
SUM(CASE Users.Gender WHEN 0 THEN 1 WHEN 1 THEN 0 END) AS ‘NumFemales’
FROM Bookings
LEFT JOIN Users ON Bookings.UserID = Users.UserID
INNER JOIN Events ON Bookings.EventID = Events.EventID
GROUP BY Events.EventID
The basic idea is as follows. Join all the tables, group by the event id, and then aggregate the fields we need to add. The twist is to select the “Gender” column twice, so we can use one to count males and the other to count females. Now if we just “SUM” each of the columns, it will show us how many men are in attendance in both columns… so we need to do a sneaky swap around on the Gender column that’s totalling the number of females so that females are represented by 1, and males by 0… that way when we do a “SUM” of each column, we will get to correct values. Thank’s to the “CASE” statement we can do this easily.
I know that I didn’t have to use the CASE statement for the count of the number of males, but I either had to do a CASE, a CAST or a CONVERT because BIT fields can’t be summed. So I just stuck with a CASE. It was a “random” choice, had nothing to do with performance, and was mainly done because I just it simply involved me doing a copy and paste from the line below.
Anyway, the point of this – remember the useful CASE statement in SQL. Its not often spoken about and probably not often used, but its very useful indeed. If you have another way of solving this, let me know… 🙂