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!
Yes, unions are weak that way - the matching isn't on column names, but rather on column positions. Hence also why some SQL's require ORDER BY 1 rather than ORDER BY custid.
Your example by fudging 0 into the matching col name solves that!
Yes, with the often-overlooked fact that a constant (like 0) is allowed in place of field name or expression. A constant is just a special case of expression.
For further edification to everyone here:
1. Though implicit in the solutions I proposed, I should clarify that a GROUP BY clause always occurs together with aggregate functions in the SELECT, and is required when there are such aggregate functions. Otherwise, the result of SELECT wouldn't be a table equivalent by SQL definition, and it is an SQL requirement that the result of SELECT be a table equivalent.
2. I think I misdescribed HAVING. I believe HAVING comes right after a GROUP BY clause and is used to include/exclude the aggregate record resulting from the GROUP BY.
Man, am I rusty on SQL!
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 25 March 2016 at 06:59, Trevor Cordes trevor@tecnopolis.ca wrote:
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! _______________________________________________ Roundtable mailing list Roundtable@muug.mb.ca http://www.muug.mb.ca/mailman/listinfo/roundtable