Link to home
Start Free TrialLog in
Avatar of jej07
jej07

asked on

Help building a query for a pivot table.

I have two tables, one for events and one for registrations. What's the best way to count how many total registrations I have so they can be displayed as seen under "Results"?

events
id_events       | short_description
2            | Event 1
15            | Event 2
35            | Event 3
      
registration_xref
event_id       | attendee_id
2            | 52
2            | 65
2            | 74
15            | 65
35            | 65
35            | 74

Results
Description      Registered
Event 1            3
Event 2            1
Event 3            2

This was my latest attempt.

$query = mysql_query("
	SELECT events.id_events, events.short_description,
	COUNT(CASE WHEN registration_xref.event_id='2' then 1 END) onecount,
	COUNT(CASE WHEN registration_xref.event_id='15' then 1 END) twocount,
	COUNT(CASE WHEN registration_xref.event_id='35' then 1 END) threecount				
	FROM events 
	LEFT JOIN registration_xref ON registration_xref.event_id = events.id_events AND event_id in ('2','15','35')
	GROUP BY events.id_events, events.short_description");

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jej07
jej07

ASKER

That's exactly what I was trying to accomplish. Thank you!