Re: [mysql] is there a way to count()/'group by' 2 things in 1 query?

Thu, 02 Apr 2009 14:40:30 -0700

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
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 
_______________________________________________
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

Reply via email to