Grouping UNION ALL

Question

No matter how I try to group the record set generated by this query it just wont do it. Query works ok the problem is trying to Group by A

SELECT     CountryCode + + ProductNumber + + StockType AS A, 
           Quantity AS B, 
           Description AS C, 
           Price AS D

FROM       dbo.vwSalesInvoiceWHandling
UNION ALL
SELECT     CountryCode + + ProductNumber + + StockType AS A,  
           Quantity AS B, 
           Description AS C, 
           Price AS D

FROM       dbo.vwSalesInvoiceWOHandling

Answer 1

You need to use a derived table or a CTE. Using derived table:

SELECT 
    A,
    SUM(B) AS SumQuantity,
    MIN(D) AS MinPrice             -- etc
FROM
  ( SELECT     CountryCode + ProductNumber + StockType AS A, 
               Quantity AS B, 
               Description AS C, 
               Price AS D

    FROM       dbo.vwSalesInvoiceWHandling
    UNION ALL
    SELECT     CountryCode + ProductNumber + StockType AS A,  
               Quantity AS B, 
               Description AS C, 
               Price AS D

    FROM       dbo.vwSalesInvoiceWOHandling
  ) 
    AS x      -- alias for the derived table
GROUP BY
    A ;

or alternatively, group by in both tables and then union all. In this case, you'll need another group by in a derived table but it will probably be more efficient, mainly because you can change the grouping by a calculated column (CountryCode + ProductNumber + StockType) to become GROUP BY CountryCode, ProductNumber, StockType:

SELECT 
    CountryCode + ProductNumber + StockType AS A,
    SUM(B) AS SumQuantity,
    MIN(D) AS MinPrice             -- etc
FROM
  ( SELECT     CountryCode, ProductNumber, StockType, 
               SUM(Quantity) AS B, 
               MIN(Price) AS D

    FROM       dbo.vwSalesInvoiceWHandling
    GROUP BY   CountryCode, ProductNumber, StockType
    UNION ALL
    SELECT     CountryCode, ProductNumber, StockType,  
               SUM(Quantity) AS B, 
               MIN(Price) AS D

    FROM       dbo.vwSalesInvoiceWOHandling
    GROUP BY   CountryCode, ProductNumber, StockType
  ) 
    AS x      -- alias for the derived table
GROUP BY
    CountryCode, ProductNumber, StockType ;