[RndTbl] SQL problem

[Trevor Cordes]

On 2016-03-25 Hartmut W Sager wrote:
> 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?

Option 1

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

Ah!  That's the trick!  I was playing with unions and gave up because
I was only trying:
select a,b 
union
select a,c

and getting frustrated that the final result wasn't giving me a,b,c but
was instead putting c values into a b labelled column!

Your example by fudging 0 into the matching col name solves that!

That may indeed be the solution, I'm changing my code now.  I was
reluctant to use subselects because (while they are working now) I am
actually pulling out 6 counts, sums and avgs in 6 subselects on where's
matching 10,000+ rows, meaning the db computation time is not optimal.

Thanks!