How to display subtotal for each group and grand

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to display subtotal for each group and grand

Author

Topic

mova_lyza
Starting Member

2 Posts

Posted - 2012-11-17 : 06:46:26

There are 2 tables

department
dept_id...dept_name
1.............IT
2.............Sports

Salary
department_id employee_id salary paid
1..................... 1......... 12000... Y
2..................... 2......... 15000... Y
2..................... 3......... 15000... N
1..................... 4......... 20000... N
1..................... 5......... 20000... Y
1..................... 6......... 20000... N
2..................... 7......... 22000... Y
2..................... 8......... 25000... N

I need the following results
For each department display,
1st column (dept id + dept name) as department
2nd column is settle and it should show the count of paid column where paid = Y for the department
3rd column is notsettled and it should show the count of paid column where paid = N for the department
4th column total – count of paid (Y + N)for the department
Then display one row after each dept the subtotal and the last row is grand total as follows
department settle notsettled total
1 IT............ 2...... 3............ 5
Total.......... 2...... 3............ 5
2 Sports...... 2...... 2............ 4
Total.......... 2...... 2............ 4
Grand Total.. 4...... 4............ 9

i have written the following query
select * from (select CAST(d.dept_id as varchar(20)) + ' ' + d.dept_name as department,
settle,notsettled,total
from department d
join (select department_id , COUNT(paid) as settle
from salary
where paid = 'y'
group by department_id
) s
on s.department_id = d.dept_id
join
(select department_id , COUNT(paid) as notsettled
from salary
where paid = 'N'
group by department_id
) s1
on s1.department_id = d.dept_id
join
(select department_id, count(paid) as total
from salary
group by department_id) s2
on s2.department_id = d.dept_id)T1

union all
select 'total',SUM(T2.settle),Sum(T2.notsettled),SUM(T2.total)
from
(select CAST(d.dept_id as varchar(20)) + ' ' + d.dept_name as department,
settle,notsettled,total
--case s.paid
--when 'Y' then COUNT (s.paid)
--End
----notsettled = case when s.paid = 'N'
--End ,
--total
from department d
join (select department_id , COUNT(paid) as settle
from salary
where paid = 'y'
group by department_id
) s
on s.department_id = d.dept_id
join
(select department_id , COUNT(paid) as notsettled
from salary
where paid = 'N'
group by department_id
) s1
on s1.department_id = d.dept_id
join
(select department_id, count(paid) as total
from salary
group by department_id) s2
on s2.department_id = d.dept_id)T2
group by t2.department

and was only able to get the results below

department settle notsettled total
1 IT............ 2...... 3............ 5
2 Sports...... 2...... 2............ 4
total.......... 2...... 3..............5
total.......... 2...... 2............ 4

need some help please

MD

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-11-17 : 19:18:54

seems to me, the subtotals are redundant or just a repetition of the grouped departments. Furthermore the calculation of subtotals and grandtotal should be done in your frontend program.

Anyway, you wanted to see how this could be done in sql (it is not pretty).
I haven't got access to my mssql server right now, so this is from the top of my head - syntax errors might be here, so please bear over with me.


select case when dept_order=1 then 'Total' else dept_name end as department
      ,settled
      ,notsettled
      ,total
  from (select d.dept_id
              ,t.dept_order
              ,concat(concat(d.dept_id,' '),d.dept_name) as dept_name
              ,sum(case when s.paid='Y' then 1 else 0 end) as settled
              ,sum(case when s.paid='N' then 1 else 0 end) as notsettled
              ,sum(1) as total
          from department as d
               inner join salery as s
                       on s.department_id=d.dept_id
               inner join (select 0 as dept_order
                           union all
                           select 1 as dept_order
                          ) as t
         group by d.dept_id
                 ,t.dept_name
                 ,d.dept_name
        union all
        select 99999999 as dept_id
              ,0 as dept_order
              ,'Grand total' as dept_name
              ,sum(case when s.paid='Y' then 1 else 0 end) as settled
              ,sum(case when s.paid='N' then 1 else 0 end) as notsettled
              ,sum(1) as total
          from department as d
               inner join salery as s
                       on s.department_id=d.dept_id
       ) as a
 order by dept_id
         ,dept_order

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-17 : 21:49:34

[code]
Select CAST(d.dept_id as varchar(20)) + ' ' + d.dept_name as department
,Sum(Case when s.paid='Y' then 1 else 0 end) as settled
,Sum(Case when s.paid='N' then 1 else 0 end) as notsettled
,Sum(Salary) as Total,
,Sum(Salary) - ((Sum(Case when s.paid='Y' then 1 else 0 end) + Sum(Case when s.paid='N' then 1 else 0 end))
from department as d
inner join salary as s on s.department_id = d.dept_id
Group by CAST(d.dept_id as varchar(20)) + ' ' + d.dept_name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources