Hi All,

I am trying to build an SQL statement that will pull data from a parent table and count rows in a child table.

Parent table - event:

eventid, title
1        'Metallica is coming'
2        'Elvis is in town'

Child table - eventattendance:

eventid,  userid,  response
1         24       Yes
1         5        Yes
2         7        Maybe
1         7        No
2         18       No
2         6        Yes

As a result of my query I am trying to have one line per event that would list attendance count for that event like this:

eventid, attendYes, attendMaybe, attendNo
1           2          0          1
2           1          1          1

So this is the query I am running:

SELECT 	eventTable.eventid AS eventID,
	COUNT(attendanceYes.response = 'Yes') AS attendYes,
	COUNT(attendanceNo.response = 'No') AS attendNo,
	COUNT(attendanceMaybe.response = 'Maybe') AS attendMaybe
FROM event AS eventTable
LEFT JOIN eventattendance AS attendanceYes
	ON ( attendanceYes.eventid = eventTable.eventid 
		AND attendanceYes.response = 'Yes' )
LEFT JOIN eventattendance AS attendanceNo
	ON ( attendanceNo.eventid = eventTable.eventid 
		AND attendanceNo.response = 'No' )
LEFT JOIN " . TABLE_PREFIX . "eventattendance AS attendanceMaybe
	ON ( attendanceMaybe.eventid = eventTable.eventid 
		AND attendanceMaybe.response = 'Maybe' )
WHERE date > $today
GROUP BY eventTable.eventid

This query does not give me the desired result. It looks like it counts the 'Yes' responses twice and places the number into both attendYes and attendNo. Can anyone suggest the proper way to do it?

9 Years
Discussion Span
Last Post by mwasif

Use this query

SELECT `event`.eventid, SUM( IF(response='Yes',1,0) ) attendYes, 
	SUM( IF(response='Maybe',1,0) ) attendMaybe,
	SUM( IF(response='No',1,0) ) attendNo

FROM `event` 
INNER JOIN eventattendance ON 
         eventattendance.eventid = `event`.eventid
GROUP BY `event`.eventid
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.