Getting the max and the min paid departments-CodePudding

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 :

To select the max and the min dept :

 Dept  Res
 3     10
 1      2

CodePudding user response：

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

CodePudding user response：

First aggregate both in sub-queries.
This gets a 1-on-1 relationship on the DeptId.
Then to get the MIN/MAX Res you can use ROW_NUMBER or DENSE_RANK.

SELECT DeptId, Res FROM ( SELECT D.DeptId, DeptAmount, ChargeAmount , CAST(1.0*DeptAmount/NULLIF(ChargeAmount, 0) AS INT) AS Res , DENSE_RANK() OVER (ORDER BY 1.0*DeptAmount/NULLIF(ChargeAmount, 0) ASC) AS RNK_ASC , DENSE_RANK() OVER (ORDER BY 1.0*DeptAmount/NULLIF(ChargeAmount, 0) DESC) AS RNK_DESC FROM ( SELECT DeptId, SUM(Amount) AS DeptAmount FROM Dept GROUP BY DeptId ) D INNER JOIN ( SELECT DeptId, SUM(Charge) AS ChargeAmount FROM Charge GROUP BY DeptId ) C ON C.DeptId = D.DeptId ) Q WHERE (RNK_ASC = 1 OR RNK_DESC = 1) ORDER BY RNK_DESC;

DeptId | Res -----: | --: 3 | 10 1 | 2

Test on db<>fiddle here