CODEWITHSUNDEEP
sqlteradata
mmcnutt
mmcnutt

Reputation: 13

Fixing Nested aggregated function

I am trying to display the productid for the product that has been sold the most (i.e, that has been sold in the highest quantity)

I have tried multiple different versions of code but every time it says cannot nest aggregated operations

SELECT productid
FROM soldvia 
GROUP BY productid
WHERE productid IN (SELECT MAX(SUM(noofitems)) FROM soldvia GROUP BY productid);

I expect the output to be PRODUCTID 3x3 4x4

Upvotes: 1

Views: 197

Answers (4)

forpas
forpas

Reputation: 164099

You can't nest aggregations.
Use ORDER BY with TOP :

SELECT TOP 1 productid 
FROM soldvia 
GROUP BY productid
ORDER BY SUM(noofitems) DESC

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In Teradata, you can use the qualify clause:

SELECT productid
FROM soldvia 
GROUP BY productid
QUALIFY ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) = 1;

This is handy. You can get duplicates by changing ROW_NUMBER() to RANK(). Actually, RANK() is more consistent with the code in your question.

Upvotes: 1

kjmerf
kjmerf

Reputation: 4345

The answer by @forpas is probably the way to go but this one is a little closer to yours:

SELECT productid
FROM soldvia
GROUP BY productid
HAVING SUM(noofitems) = (
        SELECT MAX(items)
        FROM (
            SELECT SUM(noofitems) AS items
            FROM soldvia
            GROUP BY productid
            ) x
        )

Upvotes: 0

Faseeh Haris
Faseeh Haris

Reputation: 669

Please try below query for your exact answer.

 select productid, sum(noofitems) as max_sold, 

 convert(varchar,productid) +' x '+ convert(varchar,sum(noofitems)) as

 output_sold from soldvia group by productid order by sum(noofitems) desc

Output will be

 ProductId  NoOfItemSold  Output_Sold
  1          7               1x7
  2          4               2x4
  3          1               3x1

Upvotes: 1

Related Questions