Sum Union All Queries

Transact-SQL
1

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

2

Please try notice the AS Q1:

tblStockAdjustment.AdjStockCode
) AS Q1 GROUP By StockIH
1 Like
3

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

4

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

1 Like
5

Got it! thanks