CODEWITHSUNDEEP
sqlsql-server
Guy.H
Guy.H

Reputation: 11

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY

I was looking for help in the error I get in SQL Server when I try run this query.

The query is just for splitting the products to 4 groups by 2 conditions (the quantity and the revenue). For the revenue I need the SUM function, and that's causing me problems in the GROUP BY (if I understood the error well).

How can I solve this problem?

Select
       CASE
       WHEN Ord1.Quantity<10 OR SUM(Ord1.Quantity*Ord1.UnitPrice)<50000 THEN 
       'Risky Product'
       WHEN Ord1.Quantity<30 OR SUM(Ord1.Quantity*Ord1.UnitPrice)<250000 THEN 
       'Fair Product'
       WHEN Ord1.Quantity<60 OR SUM(Ord1.Quantity*Ord1.UnitPrice)<750000 THEN 
       'Normal Product'
       Else 'Great Product'
       End As 'Group', SUM(Ord1.Quantity*Ord1.UnitPrice)

From    (Select OD.ProductID,OD.Quantity,OD.UnitPrice
         From [Order Details] As OD INNER JOIN Orders As O
         ON O.OrderID=OD.OrderID
         Where DATEPART(YEAR,O.OrderDate)=1997) As Ord1 

Group By 
         CASE
         WHEN Ord1.Quantity<10 OR SUM(Ord1.Quantity*Ord1.UnitPrice)<50000 THEN 
         'Risky Product'
         WHEN Ord1.Quantity<30 OR SUM(Ord1.Quantity*Ord1.UnitPrice)<250000 
         THEN 'Fair Product'
         WHEN Ord1.Quantity<60 OR SUM(Ord1.Quantity*Ord1.UnitPrice)<750000 THEN 
         'Normal Product'
         Else 'Great Product'
         End

Upvotes: 1

Views: 319

Answers (1)

SMor
SMor

Reputation: 2862

You have confirmed my suspicion. And I'll respond with - no point in correcting a query that is logically flawed at the beginning. You need to aggregate BEFORE you categorize. Your original approach was attempting to do both. So start over. Typically it is easier to break your problem into pieces that can be accomplished - you start at the basic level of just selecting rows, then you modify that code step-by-step to build the logic that will achieve your goal. That's appropriate for any language.

So here is my shot at an abbreviated solution. Since you did not provide a schema or data, I used AdventureWorks. In the future, help is more likely to be provided and useful when you provide schema and data or use a standard sample database. 

with cte as (
    select det.ProductID, sum(det.OrderQty * det.UnitPrice) as totalvalue 
    from Sales.SalesOrderDetail As det 
    where exists (
        select * from Sales.SalesOrderHeader as ord 
        where det.SalesOrderID = ord.SalesOrderID
          and ord.OrderDate >= '20130101' and ord.OrderDate < '20140101'
        )
    group by det.ProductID
)
select cte.ProductID, cte.totalvalue,
    case when totalvalue >= 75000 then 'Great Product' 
         else 'meh - needs work' end
    as [Group]
from cte 
order by [Group], cte.ProductID
;

Pay attention to the where clause in the cte. It is better to avoid applying a function to a column when using that as a filter since a function will prevent the use of any helpful indexes. Note also that I left out most of your logic. This demonstrates the technique; you will need to expand it for your actual needs.

Upvotes: 1

Related Questions