CODEWITHSUNDEEP
sqlsql-serversummax
user2893209
user2893209

Reputation:

trying to get the maximum value of a sum collected from different rows

I know this question is asked all the time but I am new to sql and can't seem to get an answer from other questions. I have a table (consumes_t) that has date_recieved, item_id, patient_id, quantity and time_recieved. I need to find out which item has been consumed the most.

I have written this script:

select sum (quantity) as sumquantity, item_id
from consumes_t as highquantity_t

where quantity > all

(select quantity
from consumes_t as allotherquantity_t
where highquantity_t.quantity > allotherquantity_t.quantity)

group by item_id

which gives me a list of all items and their totals but I only want to have the highest total displayed. I cannot use top1 and order by.

I've also tried:

select max (totalquantity) as consumed, item_id
from consumes_t
join (select 
    sum (consumes_t.quantity) as totalquantity 
    from consumes_t
    group by consumes_t.item_id) as t1
 on t1.totalquantity > consumes_t.quantity
 group by item_id

which gives me all item_ids with the total '9'.

Any ideas would be really appreciated. Thanks.

Upvotes: 0

Views: 53

Answers (1)

Chris Albert
Chris Albert

Reputation: 2507

I used a CTE to get the desired result

WITH A AS
(
SELECT item_id, SUM(quantity) AS TotalQuantity
FROM consumes_t
GROUP BY item_id
)

SELECT item_id, TotalQuantity
FROM A
WHERE TotalQuantity = (SELECT MAX(TotalQuantity) FROM A)

Upvotes: 1

Related Questions