[RndTbl] SQL problem

Hartmut W Sager hwsager at marityme.net
Fri Mar 25 14:07:09 CDT 2016


> 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 at 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 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/c8f66eb9/attachment.html>


More information about the Roundtable mailing list