CODEWITHSUNDEEP
sql
Bad Coder
Bad Coder

Reputation: 906

Select the Count and Maximum Value

I have the following table where the price and Units changes daily

item  Price  Units 

Milk  10    5
Milk  20    6
Milk  5     12

I want to output the Max price and total units present for each item i.e 

Milk  20.  23

My Query :

select
  item
from
  sometable
  inner join (
    select max(Price) Max_Price, item
    from sometable
    group by item
  ) as max on max.item = sometable.item and max.Max_Price = sometable.Price

How i calculate Total count efficiently as i don't want other joins.

Upvotes: 0

Views: 49

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5141

You can use below query,

select item, max(price), count(units) from table_name 
group by item;

Upvotes: 0

Alexey S. Larionov
Alexey S. Larionov

Reputation: 7927

This will do, you group over items, and for each group you select max(Price) for maximum price in a group and sum(Units) for sum of units in a group

select item, max(Price) Max_Price, sum(Units) Total_Units 
from sometable
group by item

Upvotes: 4

Related Questions