CODEWITHSUNDEEP
sql-server
Scott Davies
Scott Davies

Reputation: 103

SQL Dynamic pivot with sum on specific columns

Hi I am getting stuck on a problem where I am trying to sum up specific columns on a dynamic pivot.

For example

employee_number   last_name   first_name   salary   State   dept_id
12009             Gibson      Graham       54000    KZN     45
34974             Parker      Fred         80000    KZN     45
34987             Erickson    Neil         42000    GP      45
45001             Yates       Sally        57500    NC      30
75623             Gates       Steve        65000    GP      30
64421             Arthur      Barron       18000    GP      30
74454             James       Marther      14555    GP      25
21554             Sally       Hanson       77500    KZN     20
22132             Malcolm     Mabuna       45000    KZN     20

What I am looking for is an ability to output the sum of salary per state with a column per dept_id. The dept_id needs to be dynamic in that I may get 10 dept_id in one query and 25 in the next query. That's all fine.

What I am struggling to figure out is I need one more column that basically says if the sum of salary for dept_id 10, 11, 15, 20 and 30 is over 100,000 then 1 else 0. I had in mind to simply create the pivotted data and add up the columns. However, as you can see from this example, sometimes there is no dept_id 10, 11 or 15. When this happens, it must add up the salaries of dept_id 20 and 30. So I need a way to:

  1. Create a dynamic pivot to pivot the data per state showing sum of salary.
  2. Sum up the salary values if they exist where dept_id is 10, 11, 15, 20 or 30. 

    State   dept_id_20  dept_id_25  dept_id_30  dept_id_45    Sum   Result
    GP               0       14555       83000       42000      0        0
    KZN         122500           0           0      134000 122500        1 
    NC               0           0       57500           0      0        0

Help is appreciated!

Upvotes: 0

Views: 259

Answers (1)

Scott Davies
Scott Davies

Reputation: 103

I elected to use a different method to solve the problem. There is a finite list of possible dept_ids. So I used code similar to this:

select state, 
sum(dept_id_10), 
sum(dept_id_11),
sum(dept_id_12),
...
sum(dept_id_12),
case when sum(dept_id_10) + sum(dept_id_11) + sum(dept_id_15)
+ sum(dept_id_20) + sum(dept_id_30) > 100000 then 1 else 0) end as Result
from (
select state, 
case when dept_id = 10 then salary else 0 end as dept_id_10,
case when dept_id = 11 then salary else 0 end as dept_id_11,
case when dept_id = 12 then salary else 0 end as dept_id_12,
case when dept_id = 13 then salary else 0 end as dept_id_13,
...
case when dept_id = 45 then salary else 0 end as dept_id_45
from data)
group by state

Upvotes: 1

Related Questions