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
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 subtotal and total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mova_lyza
Starting Member

India
2 Posts

Posted - 11/17/2012 :  07:05:33  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

MD

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/18/2012 :  18:40:22  Show Profile  Reply with Quote


Duplicate thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180739
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.05 seconds. Powered By: Snitz Forums 2000