How do I break down data that I am grouping by?

Question

I have a table where each instance in the table is a sold ticket and tickets can have different ticket types. Looking something like this:

Event	Ticket Type
Event 1	a
Event 2	a
Event 1	b
Event 2	a
Event 1	a

I want it to be grouped by the event but displaying both the total tickets for that event as well as breaking down the number of each ticket type.

Event	Total Tickets	Ticket Type a	Ticket Type b
Event 1	3	2	1
Event 2	2	2	0

I have tried a few different queries but nothing that is showing me the results I'm looking for. Is this possible in one query?

Answer 1

Yes, it is possible by using GROUP BY to group the event data and then calculating the totals. The following query works by counting the number of tickets, then counting the number of tickets for each type "a" and "b".

SELECT 
    Event,
    COUNT(*) AS 'Total Tickets',
    COUNT(CASE WHEN TicketType = 'a' THEN 1 END) AS 'Ticket Type a',
    COUNT(CASE WHEN TicketType = 'b' THEN 1 END) AS 'Ticket Type b'
FROM
    Tickets
GROUP BY
    Event;