Page 1 of 1

MySql count query problem[solved]

Posted: Tue Jan 11, 2005 4:28 am
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.

Posted: Tue Jan 11, 2005 8:44 am
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

alternative

Posted: Tue Jan 11, 2005 9:09 am
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]

Posted: Tue Jan 11, 2005 9:11 am
by feyd
I totally forgot about that.. :lol:

Posted: Tue Jan 11, 2005 9:44 am
by AGISB
I knew this had the chance to teach me something entirely new.

Thanks