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
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
, SUM(s.saleamount) AS Amount
FROM itemsale AS s
JOIN salesperson AS sp
ON sp.salespersonid = s.salespersonid
JOIN salesarea AS sa
ON sa.areaid = sp.areaid
WHERE s.saledate >= '2010-01-01'
AND s.saledate <= '2010-06-30'
HAVING SUM(s.saleamount) =
( SELECT ... )
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 ?