0

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

1 Answer 1

5

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 ;
2
  • That's it, Works perfect, wow... So what is the reason a simple group by wont do the trick?
    – Nelson
    Jan 26, 2015 at 12:26
  • You can simple GROUP BY in both tables and then union. But then you'll need another group by anyway, to unite any grouped row from the first table to the one in the second. Jan 26, 2015 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.