SQL Max (Quantity*ProductPrice)

Question

I am trying to answer the question of which product ID and ProductName brought in more revenue than the average revenue earned from a product in the store.

I have the following code:

Select ProductName, SupplierName, MAX(Quantity*ProductPrice) as TotalRev
From Products
Where MAX(TotalRev) > AVG(TotalRev)

Yet this results in an error.

Sample Data

ProductID  ProductName  SupplierName ProductType Quantity ProductPrice
10001      GreenJacket  GAP          Jackets     100      $10
10002      StarEarrings Oldnavy      Accessories 200      $5
10003     YellowDress   BRP          Dress       150      $10

Ideally, I would want the code to spit out the ProductID and Product name where the product brought in more revenue that the average revenue.

Sample data and desired results would really help . . . both other people and probably you to understand the question. — Gordon Linoff, Jan 28 '19 at 01:14

score 0 · Accepted Answer · answered Jan 28 '19 at 01:28

You need a having clause and a subquery:

Select ProductId, ProductName,
       SUM(Quantity*ProductPrice) as TotalRev
From Products
group by ProductId, ProductName
having SUM(Quantity*ProductPrice) >= (select avg(revenue) 
                                      from (select sum(p2.quantity * p2.ProductPrice) as revenue
                                            from products as p2
                                            group by p2.ProductId, p2.ProductName
                                           ) as p3
                                      );

I would advise you to run the subquery in the having clause so that you fully understand what it is doing.

SQL Max (Quantity*ProductPrice)

1 Answers1