| Author |
 Topic  |
|
|
mova_lyza
Starting Member
India
2 Posts |
 Posted - 11/17/2012 : 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 |
Edited by - mova_lyza on 11/17/2012 07:02:05
|
|
|
bitsmed
Yak Posting Veteran
Denmark
98 Posts |
Posted - 11/17/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/17/2012 : 21:49:34
|
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 |
|
|
| |
Topic |
|
|
|
How to display subtotal for each group and grand
