Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Get Max value per group

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    3
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PrathimaV View Post
    ...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.
    that's what the subquery in the HAVING clause is supposed to do -- it has to provide the sum for each area

    give it a try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •