I managed to get it to work on SSMS, but it's asking me to define all the parameters when trying to create sqldatasource for my grid.
That I don't have any idea
Experts on this forum will help you
Is there any way of doing it without creating the temp table?
You can create a permanent table instead of the temp table
Did it work out Carlo ??
Curious
Hi Carlo
Scott pletchers
Way
May work... For you also
Carlo, you were very close. All I had to do was remove "centre" from the grouping and add a column for totals.
Thanks Scott, I used your option, one more question though, If I wanted the totals of each Branch as well how would I go about doing that? Like bottom line below
| Resignation Reason | Branch1 | Branch2 | Branch3 | Branch4 | Branch5 | Branch6 | Totals |
|---|---|---|---|---|---|---|---|
| Career Change | 6 | 9 | 2 | 5 | 3 | 0 | 25 |
| Career Chnage | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
| Increased salary | 4 | 8 | 0 | 0 | 1 | 0 | 13 |
| No longer wanted to teach | 4 | 5 | 0 | 0 | 1 | 0 | 10 |
| Working hours | 2 | 1 | 0 | 0 | 3 | 0 | 6 |
| Total | 16 | 23 | 3 | 5 | 8 | 0 | 55 |
hi carlo
adding rollup will put the bottom row with sum
example
SELECT
Type = ISNULL(Type, 'Total'),
TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
How would I add that to this query?
select resignationReason,
sum(case when Centre = 'BRANCH 1' then 1 else 0 end) as BRANCH1,
sum(case when Centre = 'BRANCH 2' then 1 else 0 end) as BRANCH2,
sum(case when Centre = 'BRANCH 3' then 1 else 0 end) as BRANCH3,
sum(case when Centre = 'BRANCH 4' then 1 else 0 end) as BRANCH4,
sum(case when Centre = 'BRANCH 5' then 1 else 0 end) as BRANCH5,
sum(case when Centre = 'BRANCH 6' then 1 else 0 end) as BRANCH6,
sum(1) as Totals
from HR_Exit_Interview
group by ResignationReason
Hi Carlo
I will work on it
Please give me 2 hrs
Thanks
Harish
hi carlo
i tried to do it
But got the result in another way
hope it helps
drop create data .
drop table HR_Exit_Interview
go
create table HR_Exit_Interview
(
ID int ,
Centre varchar(100),
resignationreason varchar(100)
)
go
insert into HR_Exit_Interview select 7 ,'Branch 1','Working hours'
insert into HR_Exit_Interview select 8 ,'Branch 1','Increased salary'
insert into HR_Exit_Interview select 9 ,'Branch 1','No longer wanted to teach'
insert into HR_Exit_Interview select 10 ,'Branch 1','No longer wanted to teach'
insert into HR_Exit_Interview select 11 ,'Branch 1','Career Change'
insert into HR_Exit_Interview select 12 ,'Branch 1','Career Change'
insert into HR_Exit_Interview select 15 ,'Branch 1','Career Change'
insert into HR_Exit_Interview select 16 ,'Branch 1','No longer wanted to teach'
insert into HR_Exit_Interview select 17 ,'Branch 1','Career Change'
insert into HR_Exit_Interview select 18 ,'Branch 1','Increased salary'
insert into HR_Exit_Interview select 19 ,'Branch 1','Increased salary'
insert into HR_Exit_Interview select 20 ,'Branch 1','Career Change'
insert into HR_Exit_Interview select 21 ,'Branch 1','No longer wanted to teach'
insert into HR_Exit_Interview select 23 ,'Branch 5','No longer wanted to teach'
insert into HR_Exit_Interview select 40 ,'Branch 5','Working hours'
insert into HR_Exit_Interview select 41 ,'Branch 5','Increased salary'
insert into HR_Exit_Interview select 42 ,'Branch 5','Working hours'
insert into HR_Exit_Interview select 43 ,'Branch 5','Career Change'
insert into HR_Exit_Interview select 45 ,'Branch 4','Career Change'
insert into HR_Exit_Interview select 48 ,'Branch 4','Career Change'
go
select * from HR_Exit_Interview
go
SQL ...
; WITH cte
AS (SELECT resignationreason,
Sum(CASE
WHEN centre = 'BRANCH 1' THEN 1
ELSE 0
END) AS BRANCH1,
Sum(CASE
WHEN centre = 'BRANCH 2' THEN 1
ELSE 0
END) AS BRANCH2,
Sum(CASE
WHEN centre = 'BRANCH 3' THEN 1
ELSE 0
END) AS BRANCH3,
Sum(CASE
WHEN centre = 'BRANCH 4' THEN 1
ELSE 0
END) AS BRANCH4,
Sum(CASE
WHEN centre = 'BRANCH 5' THEN 1
ELSE 0
END) AS BRANCH5,
Sum(CASE
WHEN centre = 'BRANCH 6' THEN 1
ELSE 0
END) AS BRANCH6,
Sum(1) AS Totals
FROM hr_exit_interview
GROUP BY resignationreason),
ctebottomtotals
AS (SELECT '' AS resingationreason,
Sum(branch1) AS sumbranch1,
Sum(branch2) AS sumbranch2,
Sum(branch3) AS sumbranch3,
Sum(branch4) AS sumbranch4,
Sum(branch5) AS sumbranch5,
Sum(branch6) AS sumbranch6,
Sum(totals) AS totals
FROM cte) SELECT *
FROM cte
UNION ALL
SELECT *
FROM ctebottomtotals
go
is cte a temp table?
That worked Perfectly, thank you.
yes cte is temp table
its called common table expression in T-SQL