Jump to content

Archived

This topic is now archived and is closed to further replies.

scrupul0us

Debug my query please

Recommended Posts

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

Share this post


Link to post
Share on other sites
I don't think you can conditionally COUNT like that; you may need to use a subquery.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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+.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Oops. We need a table, of course. Add your "FROM table_name."

Share this post


Link to post
Share on other sites
I thought I came across a SUMIF() function once... maybe not in MySQL?  I guess you would always use a proper IF() inside...

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.