Simplify queries

[wichtel]

Hello, I've got a query which unions two queries. Now, how can I goup the query by "DATUM" and sum up SERIES1 and SERIES2? Is this possible to write as one SQL code?
Thanks, Tom


SELECT AEST_D01.DATUM, AEST_D01.PB AS SERIES1, Null AS SERIES2
FROM AEST_D01
WHERE (AEST_D01.SERIESID)="1003"

UNION SELECT AEST_D01.DATUM, null AS SERIES1, AEST_D01.PB AS SERIES2
FROM AEST_D01
WHERE (AEST_D01.SERIESID)="1009";

 

[lupins46]

select q1.datum, sum(q1.series1) as ss1, sum(q1.series2) as ss2
from
(SELECT AEST_D01.DATUM, AEST_D01.PB AS SERIES1, Null AS SERIES2
FROM AEST_D01
WHERE (AEST_D01.SERIESID)="1003"

UNION SELECT AEST_D01.DATUM, null AS SERIES1, AEST_D01.PB AS SERIES2
FROM AEST_D01
WHERE (AEST_D01.SERIESID)="1009") as q1
group by q1.datum

 

[PHV]

Why not simply this ?
SELECT DATUM, Sum(IIf(SERIESID='1003',PB ,0)) AS SERIES1, Sum(IIf(SERIESID='1009',PB ,0)) AS SERIES2
FROM AEST_D01
GROUP BY DATUM;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lupins46]

Well done PHV - I didn't spot that the two bits of the Union were based on the same table.

 

[wichtel]

Thanks PHV and lupins46 for your great answer. But I've got a big problem: when I want to save the code from PHV in a query, the DB crashes (when I cust execute the code, it works fine). I also tried to to copy the code in another DB, but the same crash occures when I want to save. Sounds strange. Any idea?