2009/4/2 <[email protected]> > 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 >
Exquisite! Thank you! The only correction needed above is that the last arg to SUM() should be 0. SELECT p.name, COUNT(c.id) AS total, SUM(IF(SUBSTR(c.name,1,1) = 'k',1,0)) AS starts_with_k FROM parents p INNER JOIN children c ON p.id = c.parent_id GROUP BY p.name; +---------+-------+---------------+ | name | total | starts_with_k | +---------+-------+---------------+ | John | 3 | 2 | | Mary | 3 | 2 | | Susan | 3 | 0 | | Tabatha | 3 | 0 | | Vernon | 2 | 2 | +---------+-------+---------------+ 5 rows in set (0.00 sec) -- 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
