Sum of Union

[Russell1]

Hi all,

I'm having difficulty with a SQL statement that is attempting to combine a summed quantity from two tables, and hope someone can help. I am using a Merant ODBC to connect to a Progress 9.1D database via Microsoft MSQuery (which may be the real problem, but I need the results fed to Excel, for further manipulation later).

I have a working union on the two tables which returns two rows. Heres the working code:

SELECT Fis.FiscalYear, Fis.FiscalPeriod, SUM(Par.TranQty)   FROM   PUB.PartTran Par  
LEFT OUTER JOIN PUB.Fiscal Fis ON Par.TranDate>=Fis.StartDate AND Par.TranDate<=Fis.EndDate 
WHERE  (Fis.FiscalYear = 2007 and Fis.FiscalPeriod = 5)
GROUP BY Fis.FiscalYear, Fis.FiscalPeriod
UNION
SELECT Lab.FiscalYear, Lab.FiscalPeriod, Sum(Lab.LaborQty) 
FROM PUB.LaborDtl Lab
WHERE (Lab.LaborType<>'I') AND (Lab.FiscalYear=2007) AND (Lab.FiscalPeriod=5)
GROUP BY Lab.FiscalYear, Lab.FiscalPeriod

From this I get the following results:

FISCALYEAR   FISCALPERIOD  SUM(TRANQTY)
2007             5                    635535.00
2007             5                    2576652.12

The result I need is 1 overall subtotal from those two rows, i.e.:

FISCALYEAR   FISCALPERIOD  SUM(TRANQTY)
2007             5                    3212187.12

I tried wrapping another select statment around my code to get this overall total, but running it brings back the error: "Could not add the table 'SELECT'." Here's the modified non-working code:

Select FiscalYear, FiscalPeriod, sum(Qty) from
(SELECT Fis.FiscalYear, Fis.FiscalPeriod, SUM(Par.TranQty) as Qty
FROM     PUB.PartTran Par  LEFT OUTER JOIN PUB.Fiscal Fis ON Par.TranDate>=Fis.StartDate AND Par.TranDate<=Fis.EndDate 
WHERE  (Fis.FiscalYear = 2007 and Fis.FiscalPeriod = 5)
GROUP BY Fis.FiscalYear, Fis.FiscalPeriod
UNION
SELECT Lab.FiscalYear, Lab.FiscalPeriod, Sum(Lab.LaborQty) as Qty
FROM PUB.LaborDtl Lab
WHERE (Lab.LaborType<>'I') AND (Lab.FiscalYear=2007) AND (Lab.FiscalPeriod=5)
GROUP BY Lab.FiscalYear, Lab.FiscalPeriod)
GROUP BY FiscalYear, FiscalPeriod

Does anyone have any idea what is causing the error here? Or have a better way of handling this kind of problem?

Thanks,
Russell.

 

[mfg_newbie]

Select FiscalYear, FiscalPeriod, sum(Qty) 
from(
SELECT Fis.FiscalYear, Fis.FiscalPeriod, SUM(Par.TranQty) as Qty
FROM     PUB.PartTran Par  LEFT OUTER JOIN PUB.Fiscal Fis ON Par.TranDate>=Fis.StartDate AND Par.TranDate<=Fis.EndDate 
WHERE  (Fis.FiscalYear = 2007 and Fis.FiscalPeriod = 5)
GROUP BY Fis.FiscalYear, Fis.FiscalPeriod
UNION
SELECT Lab.FiscalYear, Lab.FiscalPeriod, Sum(Lab.LaborQty) as Qty
FROM PUB.LaborDtl Lab
WHERE (Lab.LaborType<>'I') AND (Lab.FiscalYear=2007) AND (Lab.FiscalPeriod=5)
GROUP BY Lab.FiscalYear, Lab.FiscalPeriod
) 
GROUP BY FiscalYear, FiscalPeriod

I think you miss an alias before GROUP BY FiscalYear, FiscalPeriod

your code become

Select FiscalYear, FiscalPeriod, sum(Qty) 
from(
SELECT Fis.FiscalYear, Fis.FiscalPeriod, SUM(Par.TranQty) as Qty
FROM     PUB.PartTran Par  LEFT OUTER JOIN PUB.Fiscal Fis ON Par.TranDate>=Fis.StartDate AND Par.TranDate<=Fis.EndDate 
WHERE  (Fis.FiscalYear = 2007 and Fis.FiscalPeriod = 5)
GROUP BY Fis.FiscalYear, Fis.FiscalPeriod
UNION
SELECT Lab.FiscalYear, Lab.FiscalPeriod, Sum(Lab.LaborQty) as Qty
FROM PUB.LaborDtl Lab
WHERE (Lab.LaborType<>'I') AND (Lab.FiscalYear=2007) AND (Lab.FiscalPeriod=5)
GROUP BY Lab.FiscalYear, Lab.FiscalPeriod
) [B][COLOR=Red]A[/COLOR][/B]
GROUP BY FiscalYear, FiscalPeriod

you try it again!

goodluck!