Re: Aggregates on empty groups.

"--CELKO--" <jcelko212_at_earthlink.net> wrote in message news:18c7b3c2.0408011922.6e46f926_at_posting.google.com...
> >> SELECT dept, SUM(sal) FROM Employees GROUP BY dept;
>
> .. and departments that are not in emp relation: Finance 0 <<
>
> But if Finance is not the Employees table, how does it get a group?
> You might build such a thing:
>
> SELECT dept, SUM(sal) FROM Employees GROUP BY dept
> UNION
> SELECT DISTINCT dept, {NULL | 0.00} FROM OtherDepartments;
>
> or something with an OUTER JOIN, but this is not a simple GROUP BY.

(I'm renaming your "OtherDepartments" to "Departments", because I don't see anything "other" about it.)

ISTM that what you want is something like

for all dept in Departments, for all sal in Employees, (dept, sum(sal))

The closest thing is an outer join with a group by, I guess, but you have to IFNULL or some such, because SQL doesn't do folds on a zero length sequence right.

Marshall Received on Mon Aug 02 2004 - 18:02:34 CEST