MySql count query problem[solved]

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
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

MySql count query problem[solved]

Post by AGISB »

I got a MYSql table with Recipes. I got 3 ENUM (TRUE,FALSE) Flags which kind of diet the recipe can be used for. Duplicate True's can be occuring.

Now I want to count the recipes for each of the 3 possible diets

Code: Select all

SELECT count(Recipe_ID) FROM Recipes WHERE softdiet = 'TRUE';
SELECT count(Recipe_ID) FROM Recipes WHERE mediumdiet = 'TRUE';
SELECT count(Recipe_ID) FROM Recipes WHERE harddiet = 'TRUE';
A php solution is

Code: Select all

$msl="SELECT softdiet, mediumdiet, harddiet FROM Recipes";
	$result=mysql_query($msl);
	while($row=mysql_fetch_assoc($result)){
	 	if($rowї'softdiet']=="TRUE"){
	 		$soft++;
		 }
		 if($rowї'mediumdiet']=="TRUE"){
	 		$medium++;
		 }
		if($rowї'harddiet']=="TRUE"){
			$hard++;
	 	}
	}
but I like to learn more about MYSQL. Is there a way to put above 3 queries into 1 getting either 3 rows with the count values or 1 row with all 3 values.
Last edited by AGISB on Tue Jan 11, 2005 9:45 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT COUNT(Recipe_ID) FROM Recipes WHERE softdiet = 'TRUE'
UNION SELECT COUNT(Recipe_ID) FROM Recipes WHERE mediumdiet = 'TRUE'
UNION SELECT COUNT(Recipe_ID) FROM Recipes WHERE harddiet = 'TRUE'
http://dev.mysql.com/doc/mysql/en/UNION.html
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

alternative

Post by Weirdan »

Code: Select all

select 
   sum(softdiet = 'TRUE') as soft_cnt, 
   sum(mediumdiet = 'TRUE') as medium_cnt, 
   sum(harddiet = 'TRUE') as hard_cnt
from  Recipes
where 'TRUE' in (softdiet, mediumdiet, harddiet)
[mysql_man]SUM[/mysql_man], [mysql_man]IN[/mysql_man]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I totally forgot about that.. :lol:
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

I knew this had the chance to teach me something entirely new.

Thanks
Post Reply