Trying to get a max or min from a table

chadsxe · #1 (**permalink**) 12-07-10, 14:11

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

r937 · #2 (**permalink**) 12-07-10, 15:01

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

chadsxe · #3 (**permalink**) 12-08-10, 10:18

Quote:

Originally Posted by r937

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?

r937 · #4 (**permalink**) 12-08-10, 10:31

Quote:

Originally Posted by chadsxe

... 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...

chadsxe · #5 (**permalink**) 12-08-10, 11:02

Quote:

Originally Posted by r937

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?

r937 · #6 (**permalink**) 12-08-10, 11:17

Quote:

Originally Posted by chadsxe

... 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 )

Internet Services

Web Development

Digital Marketing

Consumer Tech