Getting the max and the min paid departments

Question

I have the following query :

 SELECT D.Dept,(SUM(D.AMOUNT) /SUM(A.AMOUNT)) AS Res 
FROM Dept AS D
INNER JOIN Charge AS A ON D.DeptId=A.DeptId
GROUP BY D.Dept

Input : Dept table :

Dep Amount
1   300
1   300
2   1000
3   3000

Charge table :

Dep Charge
1   150
1   150
2   200
3   300

I want to calculate the sum of the amount of the salaries for each department and divide it by the charges of each department I want to have the dept having the max and the dept having the min like below :

Dept  Res
3     10
2      5
1      2  

To select the max and the min dept :

 Dept  Res
 3     10
 1      2  

Answer 1

We can use a CTE with 2 RANK ordered in opposite ways and then get the first in each direction to get the mini et maxi

create table Dept (Dept int, Amount int);
insert into dept values
(1,   200),
(1,   345),
(2,   690),
(3,   3000);
create table Charge (Dept int, Amount int);
insert into Charge values
(1,   568),
(1,   657),
(2,   300),
(3,   300);

SELECT 
  D.Dept,
  SUM(D.AMOUNT) Salaries,
  SUM(A.AMOUNT) Expenses,
  round((1e* SUM(D.AMOUNT) /SUM(A.AMOUNT)),2) AS Res 
FROM Dept AS D
JOIN Charge AS A ON D.Dept=A.Dept
GROUP BY D.Dept
ORDER BY (1e* SUM(D.AMOUNT) /SUM(A.AMOUNT)) DESC

Dept | Salaries | Expenses |  Res
---: | -------: | -------: | ---:
   3 |     3000 |      300 |   10
   2 |      690 |      300 |  2.3
   1 |     1090 |     2450 | 0.44

with allDepts as(
SELECT 
  D.Dept,
  rank() over ( order by (1e* SUM(D.AMOUNT) /SUM(A.AMOUNT)) DESC) maxi,
  rank() over (order by (1e* SUM(D.AMOUNT) /SUM(A.AMOUNT)) asc) mini,
  round((1e* SUM(D.AMOUNT) /SUM(A.AMOUNT)),2) AS Res 
FROM Dept AS D
JOIN Charge AS A ON D.Dept=A.Dept
GROUP BY D.Dept
)
select 
  Dept, Res
from allDepts
where maxi = 1
or mini = 1;

Dept |  Res
---: | ---:
   1 | 0.44
   3 |   10

db<>fiddle here