<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small;color:rgb(0,0,0)">My SQL is very rusty by now, but I did lots of SQL decades ago. Although I've read all the responses, which are largely based on the concept of correlated queries, I'm going back to your original post.<br> <br>First, a question: Do you want two separate X and Y counts for each customer, or a combined "X or Y" count for each customer?<br> <br>If the latter, then I propose:<br> <br>SELECT custid, count(*) FROM ordertable WHERE (TTL=X or TTL=Y) GROUP BY custid<br>ORDER BY custid;<br> <br>If the former, then I propose:<br> <br>SELECT custid, sum(totx), sum(toty) FROM<br>(SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid<br>UNION<br>SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid)<br>GROUP BY custid ORDER BY custid;<br> <br>If I remember correctly, unions producing a "full select" are automatically unioned with UNIQUE without specifying UNIQUE, but keep in mind that "uniqueness" is across ALL the fields in the SELECT, including aggregates like COUNT and SUM.<br> <br></div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small;color:rgb(0,0,0)">Also, in some SQL's, WHERE's in subselects must be HAVING's.<br></div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small;color:rgb(0,0,0)"> <br>If the above nesting isn't allowed in your SQL, then you'd have to use a temporary table:<br> <br>CREATE TABLE custcountxy AS<br>(SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid<br>UNION<br>SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid);<br> <br>SELECT custid, sum(totx), sum(toty) FROM custcountxy<br>GROUP BY custid ORDER BY custid;<br></div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small;color:rgb(0,0,0)">DROP TABLE custcountxy;<br></div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small;color:rgb(0,0,0)"> <br>In some SQL's and in some of the above, ORDER BY custid would need to be ORDER BY 1 (1 referring to field #1).<br> <br></div><div class="gmail_extra"><div><div class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><font size="2"><span style="font-family:verdana,sans-serif">Hartmut W Sager - Tel +1-204-339-8331, +1-204-515-1701, +1-204-515-1700, +1-810-471-4600, +1-909-361-6005<br></span></font><br></div></div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">On 24 March 2016 at 09:16, Trevor Cordes <span dir="ltr"><<a href="mailto:trevor@tecnopolis.ca" target="_blank">trevor@tecnopolis.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Can anyone help me figure out how to do this in SQL (MySQL)?<br>
<br>
(pseudo-code giving you the gist):<br>
<br>
SELECT custid, (count orders where ttl=X), (count orders where ttl=Y)<br>
FROM ordertable<br>
GROUP BY custid;<br>
<br>
<br>
I basically want to count two different things based on two different<br>
wheres. If I put in a where clause then I select either the X or the Y<br>
but I can't seem to get both in such a way I can count the X's and Y's. I<br>
tried thinking about unions but couldn't make it work. I want to do this<br>
all in 1 query as I want the sorted union of all custid's (X and Y).<br>
<br>
P.S. ordertable has/can have multiple entries for each custid.<br>
<br>
Ideas are appreciated!<br>
_______________________________________________<br>
Roundtable mailing list<br>
<a href="mailto:Roundtable@muug.mb.ca">Roundtable@muug.mb.ca</a><br>
<a href="http://www.muug.mb.ca/mailman/listinfo/roundtable" rel="noreferrer" target="_blank">http://www.muug.mb.ca/mailman/listinfo/roundtable</a><br>
</blockquote></div><br></div></div>