Group by Multiple columns

CarloT81 · July 5, 2019, 11:01am

Hi
I have an SQL table that has info on reasons why Employees left our organization. I need to group the results for each reason and by the branch. Basically like this table

Here is my Query

select Centre, (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

from HR_Exit_Interview group by centre, ResignationReason

But this is how it's being returned:

Centre	ResignationReason	Branch1	Branch3	Branch4	Branch5
Branch 1	NULL	23	0	0	0
Branch 3	NULL	0	1	0	0
Branch 5	NULL	0	0	0	2
Branch 1	Career Change	5	0	0	0
Branch 4	Career Change	0	0	3	0
Branch 5	Career Change	0	0	0	1
Branch 1	Increased salary	3	0	0	0
Branch 5	Increased salary	0	0	0	1
Branch 1	No longer wanted to teach	4	0	0	0
Branch 5	No longer wanted to teach	0	0	0	1
Branch 1	Working hours	1	0	0	0
Branch 5	Working hours	0	0	0	2

Please Help...

harishgg1 · July 5, 2019, 11:07am

hi

could you please share diagram of expected output

i am not able to understand what you want ???

CarloT81 · July 5, 2019, 11:13am

I want it grouped like the first Image

harishgg1 · July 5, 2019, 11:30am

hi

got it...

is it possible to see what the data looks like ????

from seeing that i can write the SQL

CarloT81 · July 5, 2019, 11:42am

ID	Centre	resignationreason
7	Branch 1	Working hours
8	Branch 1	Increased salary
9	Branch 1	No longer wanted to teach
10	Branch 1	No longer wanted to teach
11	Branch 1	Career Change
12	Branch 1	Career Change
15	Branch 1	Career Change
16	Branch 1	No longer wanted to teach
17	Branch 1	Career Change
18	Branch 1	Increased salary
19	Branch 1	Increased salary
20	Branch 1	Career Change
21	Branch 1	No longer wanted to teach
23	Branch 5	No longer wanted to teach
40	Branch 5	Working hours
41	Branch 5	Increased salary
42	Branch 5	Working hours
43	Branch 5	Career Change
45	Branch 4	Career Change
48	Branch 4	Career Change
56	Branch 4	Career Change

harishgg1 · July 5, 2019, 12:30pm

ok i will try to come up with the sql

harishgg1 · July 5, 2019, 1:15pm

hi

i tried to do this and got the result

hope it helps

the last totals column
you can write a seperate query and join
-- i will try this later

drop create data..

drop table #data
go

create table #data
(
ID	int ,
Centre	varchar(100),
resignationreason varchar(100)
)
go

insert into #data select 7	,'Branch 1','Working hours'
insert into #data select 8	,'Branch 1','Increased salary'
insert into #data select 9	,'Branch 1','No longer wanted to teach'
insert into #data select 10	,'Branch 1','No longer wanted to teach'
insert into #data select 11	,'Branch 1','Career Change'
insert into #data select 12	,'Branch 1','Career Change'
insert into #data select 15	,'Branch 1','Career Change'
insert into #data select 16	,'Branch 1','No longer wanted to teach'
insert into #data select 17	,'Branch 1','Career Change'
insert into #data select 18	,'Branch 1','Increased salary'
insert into #data select 19	,'Branch 1','Increased salary'
insert into #data select 20	,'Branch 1','Career Change'
insert into #data select 21	,'Branch 1','No longer wanted to teach'
insert into #data select 23	,'Branch 5','No longer wanted to teach'
insert into #data select 40	,'Branch 5','Working hours'
insert into #data select 41	,'Branch 5','Increased salary'
insert into #data select 42	,'Branch 5','Working hours'
insert into #data select 43	,'Branch 5','Career Change'
insert into #data select 45	,'Branch 4','Career Change'
insert into #data select 48	,'Branch 4','Career Change'
go

select * from #data
go

dynamic SQL ....

DROP TABLE #abc 

go 

SELECT resignationreason, 
       centre, 
       Count(*) AS countok 
INTO   #abc 
FROM   #data 
GROUP  BY resignationreason, 
          centre 

go 

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT DISTINCT ',' + Quotename(centre) 
                      FROM   #abc 
                      FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
               '') 

SET @query = N'SELECT resignationreason, ' + @cols 
             + N' from               (                 select resignationreason,Centre,countok                 from #abc             ) x             pivot              (                 max(countok)                 for Centre in (' + @cols 
             + N')             ) p ' 

PRINT @query 

EXEC Sp_executesql 
  @query;

harishgg1 · July 5, 2019, 1:31pm

ok i got the last total column also

sql dynamic with TOTALS ..

DROP TABLE #abc 

go 

SELECT resignationreason, 
       centre, 
       Count(*) AS countok 
INTO   #abc 
FROM   #data 
GROUP  BY resignationreason, 
          centre 

go 

DROP TABLE #def 

go 

SELECT resignationreason, 
       Count(*) AS countTOT 
INTO   #def 
FROM   #data 
GROUP  BY resignationreason 

go 

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT DISTINCT ',' + Quotename(centre) 
                      FROM   #abc 
                      FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
               '') 

SET @query = N'SELECT resignationreason, ' + @cols 
             + N',countTOT from               (                 select a.resignationreason,a.Centre,a.countok,b.countTOT                 from #abc a join #def b on a.resignationreason = b.resignationreason             ) x             pivot              (                 max(countok)                 for Centre in (' + @cols 
             + N')             ) p ' 

PRINT @query 

EXEC Sp_executesql 
  @query;

ScottPletcher · July 5, 2019, 2:43pm

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

CarloT81 · July 5, 2019, 9:31pm

You make it look so easy....

CarloT81 · July 5, 2019, 9:32pm

Or i made it look complicated..lol

harishgg1 · July 6, 2019, 7:31am

i have a lot (20 years) of experience

njoy

CarloT81 · July 8, 2019, 8:22am

I'm having another issue, I have 4 fields, Recruitment, Training, Induction and Recognition. Each user has to rate those sections from 1 to 5. I need to display the avg results for them in the same format as above.

harishgg1 · July 8, 2019, 9:25am

please share ... sample data

please share diagram of expected output

CarloT81 · July 8, 2019, 11:07am

Sample Data

Centre	recruitment	induction	training	recognition
Branch 1	3	3	3	3
Branch 1	3	4	3	4
Branch 1	5	4	4	3
Branch 1	2	2	5	5
Branch 1	3	3	3	3
Branch 1	4	4	4	4
Branch 1	5	5	5	5
Branch 1	5	4	4	3
Branch 1	5	4	3	3
Branch 1	4	4	4	5
Branch 1	5	5	5	5
Branch 1	5	4	4	3
Branch 1	4	1	3	1
Branch 5	5	5	5	4
Branch 5	5	4	3	3
Branch 5	5	4	5	2
Branch 5	3	4	4	4
Branch 5	3	4	5	3
Branch 4	5	4	4	4
Branch 4	5	4	4	4
Branch 4	5	5	5	5

Output needed

	Branch 1	Branch 2	Branch 3	Branch 4	Branch 5	Total
Recruitment	3.6	4.4	4.25	4.8	4.2	4.4
Induction	3.25	4.4	4	4	4.2	4.2
Training	3.8	4.3	4.5	4.4	4.4	4.2
Recognition	3.8	3.9	4.5	4.2	3.2	4

harishgg1 · July 8, 2019, 11:08am

i will work on it

please give me some time

CarloT81 · July 8, 2019, 11:16am

Thank you!

harishgg1 · July 8, 2019, 12:34pm

hi

i took a stab at this

please check verify and let me know !!!!

some things have been hard coded
... if you dont want hard coded please let me know

hope it helps .. i love feedback thanks
-)

drop create sample data ....

drop table #sampledata  
go

create table #sampledata  
(
centre varchar(100),
recruitment int ,
induction int,
training int,
recognition int
)
go

insert into #sampledata select  'Branch 1',3,3,3,3
insert into #sampledata select  'Branch 1',3,4,3,4
insert into #sampledata select  'Branch 1',5,4,4,3
insert into #sampledata select  'Branch 1',2,2,5,5
insert into #sampledata select  'Branch 1',3,3,3,3
insert into #sampledata select  'Branch 1',4,4,4,4
insert into #sampledata select  'Branch 1',5,5,5,5
insert into #sampledata select  'Branch 1',5,4,4,3
insert into #sampledata select  'Branch 1',5,4,3,3
insert into #sampledata select  'Branch 1',4,4,4,5
insert into #sampledata select  'Branch 1',5,5,5,5
insert into #sampledata select  'Branch 1',5,4,4,3
insert into #sampledata select  'Branch 1',4,1,3,1
insert into #sampledata select  'Branch 5',5,5,5,4
insert into #sampledata select  'Branch 5',5,4,3,3
insert into #sampledata select  'Branch 5',5,4,5,2
insert into #sampledata select  'Branch 5',3,4,4,4
insert into #sampledata select  'Branch 5',3,4,5,3
insert into #sampledata select  'Branch 4',5,4,4,4
insert into #sampledata select  'Branch 4',5,4,4,4
insert into #sampledata select  'Branch 4',5,5,5,5
go

sql ...

DROP TABLE #abc 

go 

; 
WITH cte 
     AS (SELECT centre, 
                recruitment   AS ko, 
                'Recruitment' AS ok 
         FROM   #sampledata 
         UNION ALL 
         SELECT centre, 
                induction, 
                'Induction' 
         FROM   #sampledata 
         UNION ALL 
         SELECT centre, 
                training, 
                'Training' 
         FROM   #sampledata 
         UNION ALL 
         SELECT centre, 
                recognition, 
                'Recognition' 
         FROM   #sampledata) 
SELECT centre, 
       ko, 
       ok 
INTO   #abc 
FROM   cte 

go 

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT DISTINCT ',' + Quotename(centre) 
                      FROM   #abc 
                      FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
               '') 

DECLARE @count1 VARCHAR 

SELECT @count1 = Count(DISTINCT centre) 
FROM   #abc 

DECLARE @cols1  AS NVARCHAR(max), 
        @query1 AS NVARCHAR(max) 

SELECT @cols1 = Stuff((SELECT DISTINCT '+' + Quotename(centre) 
                       FROM   #abc 
                       FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1 
                , '') 

SET @cols1 = 'cast((' + @cols1 + ')/' + @count1 
             + '.0 as decimal(5,2))' 
SET @query = N'SELECT ok as '' '', ' + @cols + ',' + @cols1 
             + ' as total' 
             + N' from               (                 select   centre, cast(ko as decimal(5,2)) as ko, ok         from #abc             ) x             pivot                (                 avg(ko)                 for Centre in (' 
             + @cols + N')             ) p ' 

PRINT @query 

EXEC Sp_executesql 
  @query;

CarloT81 · July 8, 2019, 1:22pm

Struggling here, Is #SamplData the name of my Table witch is actually HR_Exit_Interview. What is #abc?

harishgg1 · July 8, 2019, 1:27pm

#sampledata is your table

#abc is temp table into which I put the data in format I want

After that
I use dynamic SQL
To get the results