Jump to content


Photo

Debug my query please


  • Please log in to reply
8 replies to this topic

#1 scrupul0us

scrupul0us
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 11 July 2006 - 02:07 AM

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... nothing i do seems tomake it work.. it always returns 16

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 11 July 2006 - 02:32 AM

I don't think you can conditionally COUNT like that; you may need to use a subquery.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 scrupul0us

scrupul0us
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 11 July 2006 - 02:35 AM

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

$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");


#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 11 July 2006 - 03:03 AM

Try this:

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

You'll need (I believe) MySQL 4.0+.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 scrupul0us

scrupul0us
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 11 July 2006 - 04:17 AM

ive got 4.1.+

lemme try that out

#6 scrupul0us

scrupul0us
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 11 July 2006 - 04:23 AM

errors out on my side
$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());

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

#7 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 11 July 2006 - 04:44 AM

Oops. We need a table, of course. Add your "FROM table_name."
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 July 2006 - 08:27 AM

I thought I came across a SUMIF() function once... maybe not in MySQL?  I guess you would always use a proper IF() inside...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 SQL_F1

SQL_F1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 13 July 2006 - 07:26 AM

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))

$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");





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users