<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small;color:rgb(0,0,0)">> Ah! That's the trick! I was playing with unions and gave up because<br>> I was only trying:<br>> select a,b union select a,c<br>> and getting frustrated that the final result wasn't giving me a,b,c but<br>> was instead putting c values into a b labelled column!<br> <br>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.<br> <br>> Your example by fudging 0 into the matching col name solves that!<br> <br>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.<br> <br>For further edification to everyone here:<br> <br>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.<br> <br>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.<br> <br>Man, am I rusty on SQL!<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><br></div></div></div></div></div></div></div></div></div>
<div class="gmail_quote">On 25 March 2016 at 06:59, 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:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 2016-03-25 Hartmut W Sager wrote:<br>
> First, a question: Do you want two separate X and Y counts for each<br>
> customer, or a combined "X or Y" count for each customer?<br>
<br>
Option 1<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<br>
> TTL=X GROUP BY custid<br>
> UNION<br>
> SELECT custid, 0 as totx, count(*) as toty FROM ordertable WHERE TTL=Y<br>
> GROUP BY custid)<br>
> GROUP BY custid ORDER BY custid;<br>
<br>
Ah! That's the trick! I was playing with unions and gave up because<br>
I was only trying:<br>
select a,b<br>
union<br>
select a,c<br>
<br>
and getting frustrated that the final result wasn't giving me a,b,c but<br>
was instead putting c values into a b labelled column!<br>
<br>
Your example by fudging 0 into the matching col name solves that!<br>
<br>
That may indeed be the solution, I'm changing my code now. I was<br>
reluctant to use subselects because (while they are working now) I am<br>
actually pulling out 6 counts, sums and avgs in 6 subselects on where's<br>
matching 10,000+ rows, meaning the db computation time is not optimal.<br>
<br>
Thanks!<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>