help to query this one

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

help to query this one

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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`
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Unless your database supports subqueries, I'd suggest using a UNION.
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post by PHPycho »

Hello astions
That query just rocked
woohooooooooooooooooooo
Post Reply