Page 1 of 1

help to query this one

Posted: Mon Jun 18, 2007 11:36 pm
by PHPycho
Hello forums !!
I would like to
For example:
table "table_name" contains fields like
"field1" which contains only values "A","B"
"field2" which contains only values "X","Y"
To find the no of A in field1, no of B in field1 , no of X in field2, no of Y in field2 we can perform independent query as:

Code: Select all

$sql1 = "SELECT count(field1) AS no_of_A FROM table_name WHERE field1='A'";
$sql2 = "SELECT count(field1) AS no_of_B FROM table_name WHERE field1='B'";
$sql3 = "SELECT count(field2) AS no_of_X FROM table_name WHERE field1='X'";
$sql4 = "SELECT count(field2) AS no_of_Y FROM table_name WHERE field1='Y'";
But I want to perform above queries in one......
I would like to have somewhat

Code: Select all

$sql = "SELECT count(field1 = 'A') AS no_of_A, count(field1 = 'B') AS no_of_B, count(field2 = 'X') AS no_of_X),count(field2 = 'Y') AS no_of_Y FROM table_name WHERE field1='Y'"
I know above query doesnt work..i am just telling what i want to do..
and thats the very challenging for me..
Any comments and suggestions are warmly welcome
I am awaiting for your help.
Thanks in advance to all of you

Posted: Mon Jun 18, 2007 11:43 pm
by Benjamin

Code: Select all

SELECT SUM( IF( this_field = 'a', 1, 0 ) ) AS `this_total`,  SUM( IF( that_field = 'x', 1, 0 ) ) AS `that_total` FROM `a_table`

Posted: Mon Jun 18, 2007 11:44 pm
by feyd
Unless your database supports subqueries, I'd suggest using a UNION.

Posted: Tue Jun 19, 2007 12:10 am
by PHPycho
Hello astions
That query just rocked
woohooooooooooooooooooo