Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Unanswered: Trying to get a max or min from a table

    So I am fairly new to SQL programming and have a questions. I wrote this query

    Code:
    SELECT EMP_ID, SUM(ESTIMATE_LINE_QTY * SERVICE_COST) AS totalSales 
        FROM scf_estimate, scf_estimate_line, scf_service
        WHERE scf_estimate.ESTIMATE_ID = scf_estimate_line.ESTIMATE_ID
        AND scf_service.SERVICE_ID = scf_estimate_line.SERVICE_ID
        AND scf_estimate.ESTIMATE_ID IN
        (
            SELECT ESTIMATE_ID
                FROM scf_estimate_line
                    WHERE INVOICE_ID IS NOT NULL
        )
        GROUP BY EMP_ID;
    Which returns
    Code:
    EMP_ID  totalSales
    14	5972.2
    15	60103.35
    16	52888.5
    22	8458.5
    23	42228.8
    I am now trying to figure out how to go about and get a result that only shows the MIN or MAX of the totalSales column. I imagine I have to subquery the table that results from the query above but I am not sure on how to do that. Any advice would be great.

    Thanks

    Chad


    EDIT --------

    So I tried this

    Code:
    SELECT d.EMP_ID, MAX(d.totalSales)
    FROM
    (
        SELECT EMP_ID, SUM(ESTIMATE_LINE_QTY * SERVICE_COST) AS totalSales 
            FROM scf_estimate, scf_estimate_line, scf_service
            WHERE scf_estimate.ESTIMATE_ID = scf_estimate_line.ESTIMATE_ID
            AND scf_service.SERVICE_ID = scf_estimate_line.SERVICE_ID
            AND scf_estimate.ESTIMATE_ID IN
            (
                SELECT ESTIMATE_ID
                    FROM scf_estimate_line
                        WHERE INVOICE_ID IS NOT NULL
            )
            GROUP BY EMP_ID
    ) AS d;
    And it results in this
    Code:
    EMP_ID  MAX(d.totalSales)
    14	60103.35
    As you can see it shows the correct totalSales value but not the correct EMP_ID

    Any thoughts

    Chad

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    minimum --
    Code:
    SELECT emp_id
         , SUM(estimate_line_qty * service_cost) AS totalSales 
      FROM ...
    GROUP 
        BY emp_id
    ORDER   
        BY totalSales ASC LIMIT 1
    maximum --
    Code:
    SELECT emp_id
         , SUM(estimate_line_qty * service_cost) AS totalSales 
      FROM ...
    GROUP 
        BY emp_id
    ORDER   
        BY totalSales DESC LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by r937 View Post
    minimum --
    Code:
    SELECT emp_id
         , SUM(estimate_line_qty * service_cost) AS totalSales 
      FROM ...
    GROUP 
        BY emp_id
    ORDER   
        BY totalSales ASC LIMIT 1
    maximum --
    Code:
    SELECT emp_id
         , SUM(estimate_line_qty * service_cost) AS totalSales 
      FROM ...
    GROUP 
        BY emp_id
    ORDER   
        BY totalSales DESC LIMIT 1
    Hi - thanks for the feedback. This does work well but what happens when there are two or more MAX values?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chadsxe View Post
    ... but what happens when there are two or more MAX values?
    what happens is that you will get your answer anyway

    remember, your request was "get a result that only shows the MIN or MAX of the totalSales column"

    the solutions i offered do exactly that

    on the other hand, if you would like to restate your requirements...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2010
    Posts
    6
    Quote Originally Posted by r937 View Post
    what happens is that you will get your answer anyway

    remember, your request was "get a result that only shows the MIN or MAX of the totalSales column"

    the solutions i offered do exactly that

    on the other hand, if you would like to restate your requirements...

    I am sorry, maybe I was not clear. I understand that you will still get the max or min value but what would you need to do if you want to get all values that match the min or max?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chadsxe View Post
    ... but what would you need to do if you want to get all values that match the min or max?
    untested ...

    Code:
    SELECT scf_service.emp_id
         , SUM(scf_estimate_line.estimate_line_qty * 
               scf_service.service_cost) AS totalsales 
      FROM scf_estimate
    INNER
      JOIN scf_estimate_line
        ON scf_estimate_line.estimate_id = scf_estimate.estimate_id 
       AND scf_estimate_line.invoice_id IS NOT NULL
    INNER
      JOIN scf_service
        ON scf_service.service_id = scf_estimate_line.service_id
    GROUP 
        BY scf_service.emp_id
    HAVING SUM(scf_estimate_line.estimate_line_qty * 
               scf_service.service_cost) =
           ( SELECT MAX(totalsales)
               FROM ( SELECT scf_service.emp_id                                       
                           , SUM(scf_estimate_line.estimate_line_qty *                
                                 scf_service.service_cost) AS totalsales              
                        FROM scf_estimate                                             
                      INNER                                                           
                        JOIN scf_estimate_line                                        
                          ON scf_estimate_line.estimate_id = scf_estimate.estimate_id 
                         AND scf_estimate_line.invoice_id IS NOT NULL                 
                      INNER                                                           
                        JOIN scf_service                                              
                          ON scf_service.service_id = scf_estimate_line.service_id    
                      GROUP                                                           
                          BY scf_service.emp_id ) AS m )
    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
  •