SQLTeam.com | Weblogs | Forums

Group by Multiple columns

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 Branch2 Branch3 Branch4 Branch5 Branch6
Branch 1 NULL 23 0 0 0 0 0
Branch 3 NULL 0 0 1 0 0 0
Branch 5 NULL 0 0 0 0 2 0
Branch 1 Career Change 5 0 0 0 0 0
Branch 4 Career Change 0 0 0 3 0 0
Branch 5 Career Change 0 0 0 0 1 0
Branch 1 Increased salary 3 0 0 0 0 0
Branch 5 Increased salary 0 0 0 0 1 0
Branch 1 No longer wanted to teach 4 0 0 0 0 0
Branch 5 No longer wanted to teach 0 0 0 0 1 0
Branch 1 Working hours 1 0 0 0 0 0
Branch 5 Working hours 0 0 0 0 2 0

Please Help...

hi

could you please share diagram of expected output

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

I want it grouped like the first Image

hi

got it...

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

from seeing that i can write the SQL

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

ok i will try to come up with the sql

hi

i tried to do this and got the result

hope it helps
:slight_smile: :slight_smile:

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;

image

1 Like

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;

image

1 Like
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

You make it look so easy....

Or i made it look complicated..lol

i have a lot (20 years) of experience

njoy
:slight_smile: :slight_smile:

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.

please share ... sample data

please share diagram of expected output

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

i will work on it

please give me some time
:slight_smile:

Thank you!

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
:slight_smile: -)

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;

image

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

#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