SQL statement - multiple COUNT's from a child table

[osjak]

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
LIMIT 10

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?

[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