Hi,
I want to divide the monthly total number of scrap which I get by summing scrap records from one table by monthly quantities delivered which I get from the deliveries Table.
But sometimes there is no value for a specific month as no parts were scrapped.
To avoid this, I tried to add the monthly total scrap table to a table containing zeros for each month thanks to an Union query so that no month will be missing.
The trouble is that the query won't add zeros to values leaving me with two records for months with scraps.
In the second query after the UNION instruction I have dropped the Aliases ('AS'). The queries have sums in them and GROUP BY so that It should sum results from both queries.
ex :
SELECT PLANT_No, YEAR, MONTH, SUM(NoPARTS) AS TOTSCRAP FROM SCRAPS
GROUP BY PLANT_No, YEAR, MONTH
UNION PLANT_No, YEAR, MONTH, SUM(ZEROVALUE) FROM SCRAPS_INI
GROUP BY PLANT_No, YEAR, MONTH
Thanks.
I want to divide the monthly total number of scrap which I get by summing scrap records from one table by monthly quantities delivered which I get from the deliveries Table.
But sometimes there is no value for a specific month as no parts were scrapped.
To avoid this, I tried to add the monthly total scrap table to a table containing zeros for each month thanks to an Union query so that no month will be missing.
The trouble is that the query won't add zeros to values leaving me with two records for months with scraps.
In the second query after the UNION instruction I have dropped the Aliases ('AS'). The queries have sums in them and GROUP BY so that It should sum results from both queries.
ex :
SELECT PLANT_No, YEAR, MONTH, SUM(NoPARTS) AS TOTSCRAP FROM SCRAPS
GROUP BY PLANT_No, YEAR, MONTH
UNION PLANT_No, YEAR, MONTH, SUM(ZEROVALUE) FROM SCRAPS_INI
GROUP BY PLANT_No, YEAR, MONTH
Thanks.