|
mova_lyza
Starting Member
India
2 Posts |
 Posted - 11/17/2012 : 07:05:33
|
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 |
|
subtotal and total
