[SOLVED] Multiple Counts in one query

[rugzo]

Hi all,

 

i have a question about multiple counts in one query, i searched a lot and understood that its possible but didn't succeed.

 

$sql = mysql_query("SELECT count(gender) FROM nsnag WHERE gender = 'male' ") ;

$sql = mysql_query("SELECT count(gender) FROM nsnag WHERE gender = 'female' ") ;

 

Can i mix them up in one query like

 

$sql = mysql_query("SELECT count(if(gender=male)) as male, count(if(gender=female)) as female FROM nsnag ") ;

 

thanks...

 

 

 

[kickstart]

Hi

 

Don't think that would work.

 

You could do

 

$sql = mysql_query("SELECT (SELECT COUNT(*) FROM nsnag WHERE gender = 'male' ) AS MaleCnt, (SELECT COUNT(*) FROM nsnag WHERE gender = 'female' ) AS FemaleCnt") ;

 

But not keen on the idea.

 

Better would be to just bring back multiple lines with

 

$sql = mysql_query("SELECT COUNT(*) FROM nsnag WHERE gender IN ('male','female' ) GROUP BY gender") ;

 

(using the in clause as assuming that gender is not always set).

 

All the best

 

Keith

[gassaz]

Try this...

 

SELECT SUM(IF(gender='male',1,0)) AS 'male', SUM(IF(gender='female',1,0)) AS 'female' FROM nsnag

[rugzo]

Thanks