returning multiple comnditional counts and sums in one query

[scrupul0us]

well... i tried this:

$result = mysql_query("SELECT SUM(gCOUNT) as gINVITED, COUNT(gID) as gPARTIES, COUNT(gRSVP) where gRSVP=1 as gRSVPED, SUM(gCOUNT) where gRSVP=1 as gATTENDING from guests");
if (!$result) 
{
   die('Could not query:' . mysql_error());
}

but i got yelled at :rolleyes:

Could not query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where gRSVP=1 as gRSVPED, SUM(gCOUNT) where gRSVP=1 as gATTENDING from guests' at line 1

This is my first time doing a statement like this... and my "Welling and Thomson" isnt helping 🙁

[scrupul0us]

OK this works... kinda:

$result   = mysql_query("SELECT 
						SUM(gCOUNT) AS gINVITED, 
						COUNT(*) AS gPARTIES, 
						COUNT(gRSVP=1) AS gRSVPED, 
						SUM(gCOUNT=1)AS gATTENDING 
						FROM guests");

but for some reason: COUNT(gRSVP=1) as gRSVPED and SUM(gCOUNT=1) as gATTENDING is returning a count of every row... i know for a fact there is only one row where the value is '1'... the column type for both is tinyint... i tried using false and true as well.. still returns 16

[Drakla]

COUNT(IF(gRSVP = 1, 1, NULL))

It counts anything that's not NULL