Calculate difference of multiple highest and lowest column values

Question

I have a table like this:

id | name | salary
------------------
1  | guy1 | 1000
2  | guy2 | 750
3  | guy3 | 400
4  | guy4 | 1000
5  | guy5 | 925
6  | guy6 | 900

I need to take the highest salaries (in this case 2 * 1000) and the lowest (in this case 1 * 400), and return the difference between highest and lowest calculated like this:

1000 * 2 - 400 * 1 = 1600

difference
----------
1600

I tried to filter the table where salaries are highest and lowest but failed.

If the table is empty the result should be 0.

The Impaler · Answer 1 · 2020-11-28T15:58:59.777

1

You can do:

select
  (select sum(salary) from t where salary = (select max(salary) from t))
  -
  (select sum(salary) from t where salary = (select min(salary) from t));

Result:

See running example at DB Fiddle.

edited Nov 28 '20 at 15:58

answered Nov 28 '20 at 11:22

The Impaler

45,731
9
39
76

Thanks for the answer. It's almost right, expect the `count(*) * sum(salary)`. I changed it to `count(*) + sum(salary)` and it gave me almost the right answer, insteed of 1600 it gives me 1601. And in case of empty table it should output 0. – Mike Ehrmantraut Nov 28 '20 at 12:02
@MikeEhrmantraut - Fixed. As you can see, the answer was actually simpler. – The Impaler Nov 28 '20 at 15:59

score 1 · Answer 2 · answered Nov 28 '20 at 11:26

You could use dense_rank to find the lowest and the highest salary, and then self join those results, sum them, and subtract:

SELECT SUM(CASE sal_desc WHEN 1 THEN salary END) - 
       SUM(CASE sal_asc WHEN 1 THEN salary END)
FROM   (SELECT salary, 
               DENSE_RANK() OVER (ORDER BY salary ASC)  AS sal_asc,
               DENSE_RANK() OVER (ORDER BY salary DESC) AS sal_desc
        FROM   mytable) t

score 1 · Answer 3 · answered Nov 28 '20 at 12:20

One method is to aggregate twice:

select sum(case when seqnum_desc = 1 then sum_salary
                else - sum_salary
           end) as diff
from (select salary, sum(salary) as sum_salary,
             row_number() over (order by salary asc) as seqnum_asc,
             row_number() over (order by salary desc) as seqnum_desc
      from t
      group by salary
     ) t
where 1 in (seqnum_asc, seqnum_desc)

Erwin Brandstetter · Accepted Answer · 2022-03-21T05:25:57.810

Postgres 13 adds the WITH TIES clause to include all peers of the nth row:

Get top row(s) with highest value, with ties

If you have an index on salary, this will be as fast as it gets. Much faster than involving window functions:

SELECT COALESCE(sum(salary), 0) AS diff
FROM  (
   (  -- parentheses required
   SELECT salary
   FROM   tbl
   ORDER  BY salary DESC
   FETCH  FIRST 1 ROWS WITH TIES
   )
   UNION ALL
   (
   SELECT salary * -1
   FROM   tbl
   ORDER  BY salary
   FETCH  FIRST 1 ROWS WITH TIES
   )
   ) sub;

db<>fiddle here

Postgres can take the first and last values from an index on (salary) directly. Quasi-instantaneous result, no matter how big the table might be.

COALESCE() to get 0 instead of NULL when the table is empty.

Why the extra parentheses? The manual:

(ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

See:

This is assuming salary is NOT NULL, else append NULLS LAST to the descending order. See:

Sort by column ASC, but NULL values first?

Calculate difference of multiple highest and lowest column values

4 Answers4

Linked