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.
Author |
Topic |
mova_lyza
Starting Member
2 Posts |
Posted - 2012-11-17 : 06:46:26
|
There are 2 tablesdepartmentdept_id...dept_name1.............IT2.............SportsSalarydepartment_id employee_id salary paid1..................... 1......... 12000... Y2..................... 2......... 15000... Y2..................... 3......... 15000... N1..................... 4......... 20000... N1..................... 5......... 20000... Y1..................... 6......... 20000... N2..................... 7......... 22000... Y2..................... 8......... 25000... NI need the following resultsFor each department display,1st column (dept id + dept name) as department2nd column is settle and it should show the count of paid column where paid = Y for the department3rd column is notsettled and it should show the count of paid column where paid = N for the department4th column total – count of paid (Y + N)for the departmentThen display one row after each dept the subtotal and the last row is grand total as followsdepartment settle notsettled total1 IT............ 2...... 3............ 5Total.......... 2...... 3............ 52 Sports...... 2...... 2............ 4Total.......... 2...... 2............ 4Grand Total.. 4...... 4............ 9i have written the following query select * from (select CAST(d.dept_id as varchar(20)) + ' ' + d.dept_name as department,settle,notsettled,totalfrom department djoin (select department_id , COUNT(paid) as settlefrom salarywhere paid = 'y'group by department_id ) son s.department_id = d.dept_idjoin(select department_id , COUNT(paid) as notsettledfrom salarywhere 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 , --totalfrom department djoin (select department_id , COUNT(paid) as settlefrom salarywhere paid = 'y'group by department_id ) son s.department_id = d.dept_idjoin(select department_id , COUNT(paid) as notsettledfrom salarywhere 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)T2group by t2.departmentand was only able to get the results belowdepartment settle notsettled total1 IT............ 2...... 3............ 52 Sports...... 2...... 2............ 4total.......... 2...... 3..............5total.......... 2...... 2............ 4need some help pleaseMD |
|
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 dinner join salary as s on s.department_id = d.dept_idGroup by CAST(d.dept_id as varchar(20)) + ' ' + d.dept_name[/code] |
|
|
|
|
|
|
|