SQLTeam.com | Weblogs | Forums

Sum Union All Queries

Hi All,

I am obviously missing something, but I can not get the following to work. I know I am missing an entry at the end (before the final GROUP BY I think) but can not get to the bottom of what or why. Any pointers appreciated. Code below:

SELECT max(StockCode) as StockIH, Sum(SumQty) as TotalQuantity FROM
(
SELECT max(tblPOHeader.PODate) as MaxDate, MAX(tblPOHeader.TransType) as MaxType, tblPOLines.StockCode as StockCode, sum(tblPOLines.Quantity) as SumQty FROM tblPOHeader INNER JOIN tblPOLines ON tblPOHeader.PONo = tblPOLines.POHeaderID WHERE tblPOHeader.TransType In ('D','I','P','H','A') AND tblPOHeader.PODate>DATEADD(month, -3, GetDate()) GROUP BY tblPOLines.stockCode
UNION ALL
SELECT MAX(tblSalesHeader.SalesInvDate) as MaxDate, max(tblSalesHeader.TransType)as MaxType, tblSalesLines.StockCode as StockCode, sum(-tblSalesLines.Quantity) as SumQty FROM tblSalesHeader INNER JOIN tblSalesLines ON tblSalesHeader.SalesInvNo = tblSalesLines.SalesHeaderID WHERE tblSalesHeader.TransType In ('S','R','L') AND tblSalesHeader.SalesInvDate>DATEADD(month, -3, GetDate()) GROUP BY tblSalesLines.StockCode
UNION ALL
SELECT MAX(tblStockAdjustment.AdjDate) as MaxDate, Max(tblStockAdjustment.AdjType) as MaxType, tblStockAdjustment.AdjStockCode as StockCode, Sum(tblStockAdjustment.AdjQty) AS SumQty FROM tblStockAdjustment WHERE tblStockAdjustment.AdjDate >DATEADD(month, -3, GetDate()) GROUP BY tblStockAdjustment.AdjStockCode
) GROUP By StockIH

Please try notice the AS Q1:

tblStockAdjustment.AdjStockCode
) AS Q1 GROUP By StockIH
1 Like

Many thanks. This works well. I had just got there by adding an AS t before the GROUP BY. I still can't see the reasoning behind the additional code but there you go.

Thanks again

The "additional code" is creating an alias for the sub query. SQL needs this to handle the sub query.

1 Like

Got it! thanks