[RndTbl] SQL problem

[Hartmut W Sager]

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 at 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 at muug.mb.ca
> http://www.muug.mb.ca/mailman/listinfo/roundtable
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.muug.mb.ca/pipermail/roundtable/attachments/20160325/f5093cf5/attachment.html>