CODEWITHSUNDEEP
sqlsummaxmin
user2300392
user2300392

Reputation: 3

Summing MAX and MIN values in the same column

I am trying to pull payroll data and need to add the maximum and minimum value in a column [PAYRATE] to come up with an average rate. The table can have multiple lines for a paycheck with the same pay rate so AVG[PAYRATE] will not work. I tried the code below but it failed. Can this be done?

(SELECT SUM((MAX(payrate))+(MIN(payrate)))
FROM    PAYCHECKS
WHERE PAYROLCD in ('01', '02') 
AND PYRLRTYP = 1 
AND EMPLOYID = A.EMPLOYID 
AND PYADNMBR = A.PYADNMBR) AS PAYRATE

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Upvotes: 0

Views: 1826

Answers (2)

IndoKnight
IndoKnight

Reputation: 1864

You need to do average of Minimum and Maximum values. Also, you need to divide by decimal 2 to get accurate results otherwise if you divide just by 2, decimal result would be truncated. You may alter scale and precision according to your need below.

SELECT ((MIN(payrate)+MAX(payrate))/CAST(2 AS DECIMAL (9,2)) AS PAYRATE
FROM    PAYCHECKS
WHERE PAYROLCD in ('01', '02') 
AND PYRLRTYP = 1 
AND EMPLOYID = A.EMPLOYID 
AND PYADNMBR = A.PYADNMBR

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Remove the outer sum:

SELECT (MAX(payrate)+MIN(payrate))/2 AS PAYRATE
FROM    PAYCHECKS
WHERE PAYROLCD in ('01', '02') 
AND PYRLRTYP = 1 
AND EMPLOYID = A.EMPLOYID 
AND PYADNMBR = A.PYADNMBR

I added a /2 for the average.

Upvotes: 2

Related Questions