SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to display subtotal for each group and grand
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mova_lyza
Starting Member

India
2 Posts

Posted - 11/17/2012 :  06:46:26  Show Profile  Reply with Quote
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

Edited by - mova_lyza on 11/17/2012 07:02:05

bitsmed
Constraint Violating Yak Guru

394 Posts

Posted - 11/17/2012 :  19:18:54  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/17/2012 :  21:49:34  Show Profile  Reply with Quote

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

Edited by - sodeep on 11/17/2012 21:50:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000