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.
 
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?
 
If the latter, then I propose:
 
SELECT custid, count(*) FROM ordertable WHERE (TTL=X or TTL=Y) GROUP BY custid
ORDER BY custid;
 
If the former, then I propose:
 
SELECT custid, sum(totx), sum(toty) FROM
(SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid
UNION
SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid)
GROUP BY custid ORDER BY custid;
 
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.
 
Also, in some SQL's, WHERE's in subselects must be HAVING's.
 
If the above nesting isn't allowed in your SQL, then you'd have to use a temporary table:
 
CREATE TABLE custcountxy AS
(SELECT custid, count(*) as totx, 0 as toty FROM ordertable WHERE TTL=X GROUP BY custid
UNION
SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y GROUP BY custid);
 
SELECT custid, sum(totx), sum(toty) FROM custcountxy
GROUP BY custid ORDER BY custid;
DROP TABLE custcountxy;
 
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).
 
Hartmut W Sager - Tel +1-204-339-8331, +1-204-515-1701, +1-204-515-1700, +1-810-471-4600, +1-909-361-6005


On 24 March 2016 at 09:16, Trevor Cordes <trevor@tecnopolis.ca> wrote:
Can anyone help me figure out how to do this in SQL (MySQL)?

(pseudo-code giving you the gist):

SELECT custid, (count orders where ttl=X), (count orders where ttl=Y)
FROM ordertable
GROUP BY custid;


I basically want to count two different things based on two different
wheres.  If I put in a where clause then I select either the X or the Y
but I can't seem to get both in such a way I can count the X's and Y's.  I
tried thinking about unions but couldn't make it work.  I want to do this
all in 1 query as I want the sorted union of all custid's (X and Y).

P.S. ordertable has/can have multiple entries for each custid.

Ideas are appreciated!
_______________________________________________
Roundtable mailing list
Roundtable@muug.mb.ca
http://www.muug.mb.ca/mailman/listinfo/roundtable