How to select top 300 for each orders total order item value-CodePudding

I want to view the top 300 items ordered by total net price, how do I do this please?

Using SSMS 2014

If I remove group by I get error: Column 'orderitems.orderid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please see workings below:

select top 300

orderitems.orderid, orders.traderid, orders.orderdate, 
SUM(orderitems.nettprice) AS nettprice

from orderitems

INNER JOIN orders ON orders.tradertype = 'S' AND orders.id = 
orderitems.orderid 

where orderitems.ordertype = 'PO'

group by orderitems.orderid, orders.traderid, orders.orderdate, 
orderitems.nettprice

order by orderitems.nettprice  DESC

CodePudding user response：

You need to order by the SUM value. You can either put that in the ORDER BY explicitly, or you can use the SELECT column name without a table reference (in other words the column alias you use in the SELECT)

I strongly recommend you use short table aliases to make your code more readable

select top 300
  oi.orderid,
  o.traderid,
  o.orderdate, 
  SUM(oi.nettprice) AS nettprice
from orderitems AS oi
INNER JOIN orders AS o ON o.tradertype = 'S' AND o.id = oi.orderid 
where oi.ordertype = 'PO'
group by
  oi.orderid, o.traderid, o.orderdate
order by
  nettprice DESC
-- alternatively
order by
  SUM(oi.nettprice) DESC

CodePudding user response：

Perhaps what you need here is a windowed SUM and then a TOP. The PARTITION BY clause is based on this comment:

SELECT TOP (300)
       oi.orderid,
       o.traderid,
       o.orderdate,
       SUM(oi.nettprice) OVER (PARTITION BY oi.itemnumber, oi.partid, oi.quantity) AS totalnettprice
FROM dbo.orderitems oi
     INNER JOIN dbo.orders o ON o.id = oi.orderid
WHERE o.tradertype = 'S'
  AND oi.ordertype = 'PO'
ORDER BY oi.totalnettprice DESC;

CodePudding user response：

Very simple:

select top 300
    itm.orderid, 
    ord.traderid, 
    ord.orderdate, 
    SUM(itm.nettprice) AS price
from orderitems itm
INNER JOIN orders ord ON 
            ord.tradertype = 'S' AND ord.id = itm.orderid 
where itm.ordertype = 'PO'
group by 
    itm.orderid, 
    ord.traderid, 
    ord.orderdate
order by price DESC