Get Max value per group

PrathimaV · #1 (**permalink**) 03-31-11, 21:52

Hello All,
I have got the following query
Write an SQL query to return the sales person with the most sales (in dollars) in each sales area between 1 January 2010 and 30 June 2010. Order the results alphabetically by sales area name.

I have written a query as

select SA.Name,SP.SalespersonID,SUM(S.SaleAmount) as Amount from ItemSale S , SalesPerson SP, SalesArea SA where S.SalesPersonID = SP.SalespersonID and
S.SaleDate >='2010-01-01' and S.SaleDate <='2010-06-30' and SP.AreaID=SA.AreaID group by SA.Name,SP.SalespersonID order by SA.Name,SP.SalespersonID

am not able to do additional step of getting max sale amount for each sales area can some one help me on this

r937 · #2 (**permalink**) 03-31-11, 23:17

two things

first, this is homework, and i am somewhat dismayed to see courses still being taught in this millenium using the old-style comma joins

please, use explicit JOIN syntax

second, i'll give you a hint, you have to use a HAVING clause in order to match each salesperson's sales to "the most sales (in dollars) in each sales area" so that only the top salespersons are returned

Code:

SELECT sa.name
     , sp.salespersonid
     , SUM(s.saleamount) AS Amount 
  FROM itemsale AS s
INNER
  JOIN salesperson AS sp
    ON sp.salespersonid = s.salespersonid
INNER
  JOIN salesarea AS sa 
    ON sa.areaid = sp.areaid
 WHERE s.saledate >= '2010-01-01' 
   AND s.saledate <= '2010-06-30'  
GROUP 
    BY sa.name
     , sp.salespersonid 
HAVING SUM(s.saleamount) =
       ( SELECT ... )
ORDER 
    BY sa.name
     , sp.salespersonid

PrathimaV · #3 (**permalink**) 04-01-11, 01:03

Thanks for responding but I did tried using having clause my big question is I dont have a column which says about sales value in each sale area.First I need to SUM them and then apply max which am not able to do it. Can you help me more on this ?

Thanks in advance