SQL Query - Understanding the Syntax

Question

I want to display greatest selling product by quantity

Product Table
ProductID  ProductName
1          AA   
2          BB
3          CC
[Order Details] Table
OrderID ProductID  Quantity DateOfOrder
1       1            10    SomeDate   
2       1            100     ,,
3       2            15      ,, 
4       1            15      ,,   
5       2            20      ,, 
6       2            30      ,, 
7       1            100     ,,

Expected Output

Product By Quantity  AA

Because sum(quantity)= 225

I used:

select 'Product By Quantity' + ProductName 
from
Products 
where ProductID in
 (select 
       ProductID
  from 
       [Order Details] det 
  where Quantity=
                (
                  select max(SUM(Quantity)) 
                  from [Order Details] od
                  where
                  od.ProductID=det.ProductID
                )
  )

I got error : "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

Please explain me why the syntax fails here so that in future i will write appropriate query by knowing the correct syntax.Also give me the correct query.

Thank you everybody in advance.

Edit

I was trying for the following query

SELECT 'Best Selling Product'+ProductName
FROM 
Products
WHERE ProductID =
 (
       SELECT ProductID
       FROM [Order Details]
       GROUP BY ProductID
       HAVING SUM(Quantity) = (
                               SELECT MAX(SQ)
                               FROM (
                                       SELECT SUM(Quantity) as SQ
                                       FROM [Order Details]
                                       GROUP BY ProductID
                                    ) AS OD))

The error message says it already. You use an aggregate function inside an other aggregate function. — Mark Baijens
– Mark Baijens, Commented Sep 20, 2010 at 19:18
Can you show us the data you are dealing with and some desired output. I think your problem lies in what you are trying to do, not the max(sum()) issue. — Abe Miessler
– Abe Miessler, Commented Sep 20, 2010 at 19:20
the error is indeed putting sum() inside max(), and your answer to Martijn does not make sense. Try giving an example and we can help. — egrunin
– egrunin, Commented Sep 20, 2010 at 19:30

Joe Stefanelli · Accepted Answer · 2010-09-20 19:39:37Z

5

I think this is what you're trying to get to:

select top 1 p.product_name, sum(od.quantity) as total_quantity
    from products p
        inner join [order details] od
            on p.productid = od.productid
    group by p.productid, p.product_name
    order by total_quantity desc

edited Sep 20, 2010 at 19:39

answered Sep 20, 2010 at 19:20

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Denis Valeev Over a year ago

Group by product_name is incorrect. Say, I have a super blaster (id=4) and a super blaster (id=6) but it so happens that the latter isn't selling at all.

Joe Stefanelli Over a year ago

@Denis: Thx. I added productid to the group by clause to cover this possibility.

Denis Valeev · Accepted Answer · 2010-09-20 19:25:12Z

0

Try this:

select 'Product By Quantity' + ProductName  from Products p
join 
(
select top 1 sum(Quantity) sq, od.ProductId 
from [Order Details] od
group by od.ProductId 
order by 1 desc
) bsp on p.productid = bsp.ProductId

bsp stands for Best Selling Product

edited Sep 20, 2010 at 19:25

answered Sep 20, 2010 at 19:18

Denis Valeev

6,02537 silver badges41 bronze badges

Comments

Martijn · Accepted Answer · 2010-09-20 19:16:56Z

0

looks like select max(SUM(Quantity)) is wrong. The maximum of the sum doesn't have any meaning. Did you mean max(Quantity)?

answered Sep 20, 2010 at 19:16

Martijn

12.2k12 gold badges56 silver badges101 bronze badges

2 Comments

Amit Over a year ago

Maximum value of Sum(Quantity) of any product

JNK Over a year ago

@Amit - in SQL sum is the total of all the values returned for a column within the query...

TimS · Accepted Answer · 2010-09-20 19:38:28Z

0

Try this:

SELECT      TOP 1
            SUM(o.Quantity)
            ,p.ProductName
FROM        [Order Details] AS o
INNER JOIN  [Products] AS p ON p.ProductID = o.ProductID
GROUP BY    p.ProductID
            ,p.ProductName
ORDER BY    SUM(o.Quantity) DESC

edited Sep 20, 2010 at 19:38

answered Sep 20, 2010 at 19:22

TimS

6,0426 gold badges38 silver badges56 bronze badges

1 Comment

Joe Stefanelli Over a year ago

Close, but not quite right. You're ordering by the individual quantities rather than the sum. See my answer.

Collectives™ on Stack Overflow

SQL Query - Understanding the Syntax

Edit

4 Answers 4

2 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

4 Answers 4

2 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related