Debug my query please

[scrupul0us]

OK this works... kinda:

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

but for some reason: [B]COUNT(gRSVP=1) as gRSVPED[/B] and [B]SUM(gCOUNT=1) as gATTENDING[/B] 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... nothing i do seems tomake it work.. it always returns 16

[effigy]

I don't think you can conditionally COUNT like that; you may need to use a subquery.

[scrupul0us]

whats the easiest and cleanest way todo that b/c as i see it im stuck doing:

[code]
$result1  = mysql_query("SELECT SUM(gCOUNT) AS gINVITED, COUNT(*) AS gPARTIES FROM guests");
$result2  = mysql_query("SELECT COUNT(gRSVP) AS gRSVPED FROM guests WHERE gRSVP = 1");
$result3  = mysql_query("SELECT SUM(gCOUNT) AS gATTENDING FROM guests WHERE gCOUNT = 1");
[/code]

[effigy]

Try this:

[code]SELECT
SUM(gCOUNT) AS gINVITED,
COUNT(*) AS gPARTIES,
(SELECT COUNT(*) WHERE gRSVP=1) AS gRSVPED,
(SELECT SUM(gCOUNT) WHERE gCOUNT=1) AS gATTENDING
FROM guests[/code]

You'll need (I believe) MySQL 4.0+.

[scrupul0us]

ive got 4.1.+

lemme try that out

[scrupul0us]

errors out on my side
[code]
$result  = mysql_query("SELECT
SUM(gCOUNT) AS gINVITED,
COUNT(*) AS gPARTIES,
(SELECT COUNT(*) WHERE gRSVP=1) AS gRSVPED,
(SELECT SUM(gCOUNT) WHERE gATTEND=1) AS gATTENDING
FROM guests") or die(mysql_error());
[/code]

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, (SELECT SUM(gCOUNT) WHERE gATTEND=1) AS gATTE' at line 4

Where line 4 is where the sub-query starts

[effigy]

Oops. We need a table, of course. Add your "FROM table_name."

[fenway]

I thought I came across a SUMIF() function once... maybe not in MySQL?  I guess you would always use a proper IF() inside...

[SQL_F1]

The purpose of the gCount field is unclear from your query so I've guessed that gATTENDING is the gCOUNT of people who have RSVP'd positively.

COUNT(*) counts all records in the result set.
COUNT(<VALUE>) COUNTS non-NULL instances of <VALUE>
To "count" the number of qualifying records, use SUM(IF(<boolean expression>, 1, 0))

[code]$result  = mysql_query("SELECT
SUM(gCOUNT) AS gINVITED,
COUNT(*) AS gPARTIES,
SUM(IF(gRSVP=1, 1, 0)) AS gRSVPED,
SUM(IF(gRSVP=1, gCOUNT, 0))AS gATTENDING
FROM guests");[/code]