very sweet, I temporarily blanked out on functions, if/else and case in SQL.
there is no language like SQL that delivers just the most impressive and valuable results for the most frugal code input. SQL is the undisputed. Warmest regards, Peter Sawczynec Technology Dir. blūstudio 941.893.0396 <mailto:[email protected]> [email protected] www.blu-studio.com From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Thursday, April 02, 2009 5:40 PM To: MySQL SIG Subject: [Norton AntiSpam] Re: [mysql] is there a way to count()/'group by' 2 things in 1 query? A real quick example with no testing: SELECT p.name, COUNT(c.id) AS total, SUM(IF(SUBSTR(c.name,1,1) = 'k',1,1)) AS starts_with_k FROM parents p INNER JOIN children c ON p.id = c.parent_id GROUP BY p.name -------- Original Message -------- Subject: Re: [mysql] is there a way to count()/'group by' 2 things in 1 query? From: "Peter Sawczynec" <[email protected]> Date: Thu, April 02, 2009 3:47 pm To: "'MySQL SIG'" <[email protected]> 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 <mailto:[email protected]> [email protected] www.blu-studio.com From: [email protected] [ <mailto:[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 <http://parents.name> parents.name, count( <http://children.id> children.id) as `total children` FROM children JOIN parents ON children.parent_id = <http://parents.id> parents.id GROUP BY <http://parents.name> parents.name; +---------+----------------+ | name | total children | +---------+----------------+ | John | 3 | | Mary | 3 | | Susan | 3 | | Tabatha | 3 | | Vernon | 2 | +---------+----------------+ SELECT <http://parents.name> parents.name, count( <http://children.id> children.id) as `total children whose names begin with K` FROM parents JOIN children ON children.parent_id = <http://parents.id> parents.id WHERE <http://children.name> children.name LIKE 'k%' GROUP BY <http://parents.name> 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/> 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> http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online <http://www.nyphpcon.com> http://www.nyphpcon.com Show Your Participation in New York PHP <http://www.nyphp.org/show_participation.php> http://www.nyphp.org/show_participation.php
_______________________________________________ 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
