One can try something similar to this maybe where one of your returned fields is actually another aliased select statement.
But I may not have the exact technical phrasing correct here : SELECT parents.name, count(children.id) as `total children`, (SELECT count(children.id) FROM parents JOIN children ON children.parent_id = parents.id WHERE children.name LIKE 'k%') as `total children whose names begin with K` FROM children JOIN parents ON children.parent_id = parents.id GROUP BY parents.name Warmest regards, Peter Sawczynec Technology Dir. blūstudio 941.893.0396 [email protected] <mailto:[email protected]> www.blu-studio.com From: [email protected] [mailto:[email protected]] On Behalf Of David Mintz Sent: Thursday, April 02, 2009 11:57 AM To: [email protected] Subject: [mysql] is there a way to count()/'group by' 2 things in 1 query? Hello Assume two tables: parents and children. : CREATE TABLE `parents` ( `id` smallint(5) unsigned NOT NULL auto_increment, `name` varchar(30) default NULL, PRIMARY KEY (`id`) ) CREATE TABLE `children` ( `id` smallint(5) unsigned NOT NULL auto_increment, `parent_id` smallint(5) unsigned NOT NULL, `name` varchar(30) default NULL, PRIMARY KEY (`id`) Suppose I want to find how many children each parent has, AND how many children each parent has whose name begins with 'k'. SELECT parents.name, count(children.id) as `total children` FROM children JOIN parents ON children.parent_id = parents.id GROUP BY parents.name; +---------+----------------+ | name | total children | +---------+----------------+ | John | 3 | | Mary | 3 | | Susan | 3 | | Tabatha | 3 | | Vernon | 2 | +---------+----------------+ SELECT parents.name, count(children.id) as `total children whose names begin with K` FROM parents JOIN children ON children.parent_id = parents.id WHERE children.name LIKE 'k%' GROUP BY parents.name; +---------+-----------------------------------------+ | name | total children whose names begin with K | +---------+-----------------------------------------+ | John | 2 | | Mary | 2 | | Vernon | 2 | +-------- +-----------------------------------------+ Is there a way to get all this information in one query? I also wonder if it's possible to return rows for which the count(*) is zero, like so: +---------+-----------------------------------------+ | name | total children whose names begin with K | +---------+-----------------------------------------+ | John | 2 | | Mary | 2 | | Vernon | 2 | | Susan | 0 | | Tabatha | 0 | +-------- +-----------------------------------------+ SELECT REPEAT( "Thank you! ", 1000); -- David Mintz http://davidmintz.org/ The subtle source is clear and bright The tributary streams flow through the darkness
_______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
