[prev in list] [next in list] [prev in thread] [next in thread]
List: nyphp-mysql
Subject: Re: [mysql]
From: dirn () dirnonline ! com
Date: 2009-04-02 21:40:17
Message-ID: 20090402144017.9562cbc3556ac68f081dfda387a9f4ab.ed559c2c26.wbe () email02 ! secureserver ! net
[Download RAW message or body]
[Attachment #2 (unknown)]
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;">A real \
quick example with no testing:<br><br>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<br><br> <blockquote webmail="1" \
style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size: \
10pt; color: black; font-family: verdana;"> <div >
-------- Original Message --------<br>
Subject: Re: [mysql] is there a way to count()/'group by' 2 things in 1<br>
query?<br>
From: "Peter Sawczynec" <ps@blu-studio.com><br>
Date: Thu, April 02, 2009 3:47 pm<br>
To: "'MySQL SIG'" <mysql@lists.nyphp.org><br>
<br>
<style>
#wmMessage /* Font Definitions */ @font-face {font-family:"Cambria Math"; \
panose-1:2 4 5 3 5 4 6 3 2 4;} #wmMessage @font-face {font-family:Calibri; \
panose-1:2 15 5 2 2 2 4 3 2 4;} #wmMessage @font-face {font-family:Tahoma; \
panose-1:2 11 6 4 3 5 4 4 2 4;} #wmMessage /* Style Definitions */ p.MsoNormal, \
#wmMessage li.MsoNormal, #wmMessage div.MsoNormal {margin:0in; \
margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman","serif";} \
#wmMessage a:link, #wmMessage span.MsoHyperlink {mso-style-priority:99; color:blue; \
text-decoration:underline;} #wmMessage a:visited, #wmMessage \
span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; \
text-decoration:underline;} #wmMessage span.EmailStyle17 \
{mso-style-type:personal-reply; font-family:"Calibri","sans-serif"; color:#1F497D;} \
#wmMessage .MsoChpDefault {mso-style-type:export-only;} #wmMessage @page Section1 \
{size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} #wmMessage div.Section1 \
{page:Section1;}
</style> <div class="Section1"> <div><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">One can try something similar to this maybe where one of \
your returned fields is actually another aliased select \
statement.<o:p></o:p></font></div> <div><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">But I may not have the exact technical phrasing correct \
here : <o:p></o:p></font></div> <div><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif"><o:p> </o:p></font></div> <div><font style="font-size: \
11pt; font-family: "Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">SELECT parents.name, <o:p></o:p></font></div> <div><font \
style="font-size: 11pt; font-family: "Calibri","sans-serif";" \
color="#1f497d" face="Calibri,sans-serif">count(children.id) as `total \
children`, <o:p></o:p></font></div> <div><font style="font-size: 11pt; \
font-family: "Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">(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` <o:p></o:p></font></div> <div><font style="font-size: \
11pt; font-family: "Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">FROM children JOIN parents ON children.parent_id = \
parents.id <o:p></o:p></font></div> <div><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">GROUP BY parents.name<o:p></o:p></font></div> <div><font \
style="font-size: 11pt; font-family: "Calibri","sans-serif";" \
color="#1f497d" face="Calibri,sans-serif"><o:p> </o:p></font></div> <div><font \
style="font-family: "Arial","sans-serif";" color="#1f497d" \
face="Arial,sans-serif" size="2">Warmest regards, </font><font style="font-size: \
11pt; font-family: "Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif"><o:p></o:p></font></div> <div><font style="font-size: 11pt; \
font-family: "Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif"> <o:p></o:p></font></div> <div><font \
style="font-family: "Arial","sans-serif";" color="#1f497d" \
face="Arial,sans-serif" size="2">Peter Sawczynec </font><font style="font-size: 11pt; \
font-family: "Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif"><o:p></o:p></font></div> <div><font style="font-family: \
"Arial","sans-serif";" color="#1f497d" face="Arial,sans-serif" \
size="2">Technology Dir.<o:p></o:p></font></div> <div><font style="font-family: \
"Arial","sans-serif";" color="#1f497d" face="Arial,sans-serif" \
size="2">bl</font><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif">รป</font><font style="font-family: \
"Arial","sans-serif";" color="#1f497d" face="Arial,sans-serif" \
size="2">studio </font><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif"><o:p></o:p></font></div> <div><font style="font-family: \
"Arial","sans-serif";" color="#1f497d" face="Arial,sans-serif" \
size="2">941.893.0396</font><font style="font-size: 11pt; font-family: \
"Calibri","sans-serif";" color="#1f497d" \
face="Calibri,sans-serif"><o:p></o:p></font></div> <div><font style="font-family: \
"Arial","sans-serif";" color="#1f497d" face="Arial,sans-serif" \
size="2"><a onclick="return \
true;if(window.location==top.location){Popup.composeWindow('pcompose.php?sendto=ps%40s \
un-code.com');}else{top.Popup.composeWindow('pcompose.php?sendto=ps%40sun-code.com');}; \
return false;" href="mailto:ps@sun-code.com" target="_blank" \
mce_href="mailto:ps@sun-code.com">ps@blu-studio.com</a> <o:p></o:p></font></div> \
<div><font style="font-family: "Arial","sans-serif";" \
color="#1f497d" face="Arial,sans-serif" size="2">www.blu-studio.com </font><font \
style="font-size: 11pt; font-family: "Calibri","sans-serif";" \
color="#1f497d" face="Calibri,sans-serif"><o:p></o:p></font></div> <div><font \
style="font-size: 11pt; font-family: "Calibri","sans-serif";" \
color="#1f497d" face="Calibri,sans-serif"><o:p> </o:p></font></div> <div><font \
style="font-size: 11pt; font-family: "Calibri","sans-serif";" \
color="#1f497d" face="Calibri,sans-serif"><o:p> </o:p></font></div> <div \
style="border-style: solid none none; border-color: rgb(181, 196, 223) \
-moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: \
3pt 0in 0in;"> <div><b><font style="font-family: \
"Tahoma","sans-serif";" face="Tahoma,sans-serif" \
size="2">From:</font></b><font style="font-family: \
"Tahoma","sans-serif";" face="Tahoma,sans-serif" size="2"> \
mysql-bounces@lists.nyphp.org [<a onclick="return \
true;if(window.location==top.location){Popup.composeWindow('pcompose.php?sendto=mysql- \
bounces%40lists.nyphp.org');}else{top.Popup.composeWindow('pcompose.php?sendto=mysql-bounces%40lists.nyphp.org');}; \
return false;" href="mailto:mysql-bounces@lists.nyphp.org" target="_blank" \
mce_href="mailto:mysql-bounces@lists.nyphp.org">mailto:mysql-bounces@lists.nyphp.org</a>] \
<b>On Behalf Of </b>David Mintz<br> <b>Sent:</b> Thursday, April 02, 2009 11:57 \
AM<br> <b>To:</b> mysql@lists.nyphp.org<br> <b>Subject:</b> [mysql] is there a way to \
count()/'group by' 2 things in 1 query?<o:p></o:p></font></div> </div> \
<div><o:p> </o:p></div> <div>Hello<br> <br> Assume two tables: parents and \
children. <br> <br> : CREATE TABLE `parents` (<br> `id` smallint(5) unsigned \
NOT NULL auto_increment,<br> `name` varchar(30) default NULL,<br> \
PRIMARY KEY (`id`)<br> ) <br> CREATE TABLE `children` (<br> `id` \
smallint(5) unsigned NOT NULL auto_increment,<br> `parent_id` smallint(5) \
unsigned NOT NULL,<br> `name` varchar(30) default NULL,<br> PRIMARY \
KEY (`id`)<br> <br clear="all"> <br> Suppose I want to find how many children \
each parent has, AND how many children each parent has whose name begins with 'k'. \
<br> <br> SELECT <a href="http://parents.name" target="_blank" \
mce_href="http://parents.name">parents.name</a>, count(<a href="http://children.id" \
target="_blank" mce_href="http://children.id">children.id</a>) as `total children` \
FROM children JOIN parents ON children.parent_id = <a href="http://parents.id" \
target="_blank" mce_href="http://parents.id">parents.id</a> GROUP BY <a \
href="http://parents.name" target="_blank" \
mce_href="http://parents.name">parents.name</a>;<br> <font style="font-family: \
"Courier New";" face="Courier New">+---------+----------------+<br> | \
name | total children |<br> +---------+----------------+<br> | \
John \
| 3 | \
<br> | Mary \
| 3 | \
<br> | Susan \
| 3 | \
<br> | Tabatha | \
3 | <br> | Vernon \
| 2 | \
<br> +---------+----------------+<br> <br> </font>SELECT <a \
href="http://parents.name" target="_blank" \
mce_href="http://parents.name">parents.name</a>, count(<a href="http://children.id" \
target="_blank" mce_href="http://children.id">children.id</a>) as `total children \
whose names begin with K` FROM parents JOIN children ON children.parent_id = <a \
href="http://parents.id" target="_blank" mce_href="http://parents.id">parents.id</a> \
WHERE <a href="http://children.name" target="_blank" \
mce_href="http://children.name">children.name</a> LIKE 'k%' GROUP BY <a \
href="http://parents.name" target="_blank" \
mce_href="http://parents.name">parents.name</a>;<br> <br> <font style="font-family: \
"Courier New";" face="Courier \
New">+---------+-----------------------------------------+<br> | \
name | total children whose names begin with K |<br> \
+---------+-----------------------------------------+<br> | John \
| & \
nbsp; \
2 | <br> | Mary \
| & \
nbsp; \
2 | <br> | Vernon \
| & \
nbsp; \
2 | <br> +-------- +-----------------------------------------+</font><br> <br> Is \
there a way to get all this information in one query? <br> <br> I also wonder if it's \
possible to return rows for which the count(*) is zero, like so:<br> <br> <font \
style="font-family: "Courier New";" face="Courier \
New">+---------+-----------------------------------------+<br> | \
name | total children whose names begin with K |</font><br> <font \
style="font-family: "Courier New";" face="Courier \
New">+---------+-----------------------------------------+<br> | \
John \
| & \
nbsp; \
2 | <br> | Mary \
| & \
nbsp; \
2 | <br> | Vernon \
| & \
nbsp; \
2 | <br> | Susan | \
&n \
bsp; \
0 | </font><br> <font style="font-family: "Courier New";" face="Courier \
New">| Tabatha | &nb \
sp; \
0 | </font><br> <font style="font-family: "Courier New";" face="Courier \
New">+-------- +-----------------------------------------+</font><br> <br> SELECT \
REPEAT( "Thank you! ", 1000);<br> <br> -- <br> David Mintz<br> <a \
href="http://davidmintz.org/" target="_blank" \
mce_href="http://davidmintz.org/">http://davidmintz.org/</a><br> <br> The subtle \
source is clear and bright<br> The tributary streams flow through the \
darkness<o:p></o:p></div> </div> \
<hr>_______________________________________________<br> New York PHP Community MySQL \
SIG<br> <a href="http://lists.nyphp.org/mailman/listinfo/mysql" target="_blank" \
mce_href="http://lists.nyphp.org/mailman/listinfo/mysql">http://lists.nyphp.org/mailman/listinfo/mysql</a><br>
<br>
NYPHPCon 2006 Presentations Online<br>
<a href="http://www.nyphpcon.com" target="_blank" \
mce_href="http://www.nyphpcon.com">http://www.nyphpcon.com</a><br> <br>
Show Your Participation in New York PHP<br>
<a href="http://www.nyphp.org/show_participation.php" target="_blank" \
mce_href="http://www.nyphp.org/show_participation.php">http://www.nyphp.org/show_participation.php</a>
</div>
</blockquote></span></body></html>
_______________________________________________
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic