How to Find Percentage using a recursive cte

• timotech

  SSCrazy

  Points: 2698

  More actions

  April 22, 2016 at 11:35 am

  #310179

  Hi all,

  Please i have this cte:

  declare @Parameter nvarchar(4000)

  set @Parameter = 'Urbanisation'

  ;with tmp as (

  Select Count(*) as Base, DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar

  FROM TestTable

  )

  ,

  tmp2 as (

  Select 'Criteria' as Criteria,

  SUM(Base) as Base,

  SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

  SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

  SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

  SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

  SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

  SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

  SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

  SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

  SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

  SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

  SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

  SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

  SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

  SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

  SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

  SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

  SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

  SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

  SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

  SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

  SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

  SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

  SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

  SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

  SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

  SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

  From tmp

  union all

  Select Criteria, (t.Base/tmp.Base) * 100, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday,Urban, [Semi-Urban], Male, Female, [8-11years], [12-17years], [18-24years], [25-34years], [35-44years], [45-54years], [55-64years], [65+years], Christian, Muslim, Others, Single, Married, Widowed, Divorced from (

  Select case when @Parameter = 'DayOfInterview' then DayOfInterview

  when @Parameter = 'Gender' then Gender

  when @Parameter = 'Urbanisation' then Urbanisation

  when @Parameter = 'AgeGroup' then AgeGroup

  when @Parameter = 'Religion' then Religion

  when @Parameter = 'MaritalStatus' then MaritalStatus

  End As Criteria,

  sum(Base) as Base,

  SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

  SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

  SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

  SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

  SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

  SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

  SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

  SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

  SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

  SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

  SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

  SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

  SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

  SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

  SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

  SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

  SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

  SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

  SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

  SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

  SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

  SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

  SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

  SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

  SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

  SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

  From tmp

  group by case when @Parameter = 'DayOfInterview' then DayOfInterview

  when @Parameter = 'Gender' then Gender

  when @Parameter = 'Urbanisation' then Urbanisation

  when @Parameter = 'AgeGroup' then AgeGroup

  when @Parameter = 'Religion' then Religion

  when @Parameter = 'MaritalStatus' then MaritalStatus

  End) as t

  )

  Select Criteria, Base, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday,Urban, [Semi-Urban], Male, Female, [8-11years], [12-17years], [18-24years], [25-34years], [35-44years], [45-54years], [55-64years], [65+years], Christian, Muslim, Others, Single, Married, Widowed, Divorced from tmp2

  I getting this error:

  The multi-part identifier "tmp.Base" could not be bound.

  Because i want to get percentages instead of just plain values, so when i divide the (t.Base/tmp.Base) * 100, it tells me that the tmp.Base cannot be bound.

  Please what can i do, this is my sample data below:

  insert into TestTable(RespondentName,PhoneNumber1,MaritalStatus,Region,Urbanisation,Religion,Gender,Location,AgeGroup,DayOfInterview,PhoneUsed,TabletsUsed)

  Select 'kome','07051758008','Single','South-South','Urban','Christian','Male','Lagos','18-24','Friday','Android Phone','None'

  union all

  Select 'joy', 'Kofi','09099961220','Single','South-South','Urban','Christian','Female','Lagos','18-24','Saturday','Android Phone','None'

  union all

  Select 'taiwo akinwunmi','07056832797','Single','South-South','Urban','Christian','Male','Lagos','25-34','Saturday','BlackBerry','None'

  union all

  Select 'blessing','08182960051','Married','South-South','Urban','Christian','Female','Lagos','65+','Saturday','BlackBerry','None'

  union all

  Select 'blessing','08182960051','Married','South-South','Urban','Christian','Female','Lagos','65+''Saturday','Android Phone','None'

  union all

  Select 'Oyinlola Idowu','08109096652','Single','South-West','Semi-Urban','Christian','Female','Lagos','18-24','Saturday','Android Phone','None'

  union all

  Select 'Ibukun Olaoluwa','07051785755','Married','South-West','Urban','Christian','Female','Lagos','25-34','Sunday','BlackBerry','None'

  union all

  Select 'Ibukun Olaoluwa','07051785755','Married','South-West','Urban','Christian','Female','Lagos','25-34','Sunday','Android Phone','None'

  union all

  Select 'Philip Agbo','08086809359','Single','South-South','Semi-Urban','Christian','Male','Lagos','18-24','Sunday''nokia','None'

  union all

  Select 'ukpebor festus','08069148341','Single','South-South','Semi-Urban','Christian','Male','Lagos','8-11','Sunday','Regular Phone','None'

  union all

  Select 'Victoria Egemonu','07030213914','Widowed','South-South','Semi-Urban','Muslim','Male','Benin','8-11','Tuesday','TECNO','None'

  union all

  Select 'Victoria Egemonu','07030213914','Widowed','South-South','Semi-Urban','Muslim','Male','Benin','8-11','Tuesday','Tecno','None'

  union all

  Select 'emmanuel','08091475363','Single','South-West','Urban','Christian','Male','Lagos','25-34','Friday','BlackBerry','Microsoft', 'Tablet'

  Thanks

  Tim

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 22, 2016 at 12:30 pm

  #1874000

  Please post DDL (CREATE TABLE statement) for the table.

  Edit:

  Correct your sample data, be sure it runs correctly.

  Also, share what you're trying to do with this data. You might not need a recursive cte at all.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• timotech

  SSCrazy

  Points: 2698

  More actions

  April 22, 2016 at 4:38 pm

  #1874072

  Luis Cazares (4/22/2016)

  ---

  Please post DDL (CREATE TABLE statement) for the table.

  Edit:

  Correct your sample data, be sure it runs correctly.

  Also, share what you're trying to do with this data. You might not need a recursive cte at all.

  Thanks Luis,

  i've corrected the errors and this is the code and the data:

  declare @Parameter nvarchar(4000)

  set @Parameter = 'Urbanisation'

  ;with tmp as (

  Select Count(*) as Base, DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar

  FROM TestTable

  group by DayOfInterview,Urbanisation, Gender, AgeGroup, Religion, MaritalStatus

  )

  ,

  tmp2 as (

  Select 'Criteria' as Criteria,

  SUM(Base) as Base,

  SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

  SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

  SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

  SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

  SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

  SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

  SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

  SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

  SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

  SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

  SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

  SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

  SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

  SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

  SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

  SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

  SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

  SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

  SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

  SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

  SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

  SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

  SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

  SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

  SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

  SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

  From tmp

  union all

  Select Criteria, (t.Base/tmp.Base) * 100, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday,Urban, [Semi-Urban], Male, Female, [8-11years], [12-17years], [18-24years], [25-34years], [35-44years], [45-54years], [55-64years], [65+years], Christian, Muslim, Others, Single, Married, Widowed, Divorced from (

  Select case when @Parameter = 'DayOfInterview' then DayOfInterview

  when @Parameter = 'Gender' then Gender

  when @Parameter = 'Urbanisation' then Urbanisation

  when @Parameter = 'AgeGroup' then AgeGroup

  when @Parameter = 'Religion' then Religion

  when @Parameter = 'MaritalStatus' then MaritalStatus

  End As Criteria,

  sum(Base) as Base,

  SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

  SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

  SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

  SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

  SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

  SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

  SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

  SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

  SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

  SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

  SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

  SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

  SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

  SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

  SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

  SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

  SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

  SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

  SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

  SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

  SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

  SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

  SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

  SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

  SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

  SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

  From tmp

  group by case when @Parameter = 'DayOfInterview' then DayOfInterview

  when @Parameter = 'Gender' then Gender

  when @Parameter = 'Urbanisation' then Urbanisation

  when @Parameter = 'AgeGroup' then AgeGroup

  when @Parameter = 'Religion' then Religion

  when @Parameter = 'MaritalStatus' then MaritalStatus

  End) as t

  )

  Select Criteria, Base, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday,Urban, [Semi-Urban], Male, Female, [8-11years], [12-17years], [18-24years], [25-34years], [35-44years], [45-54years], [55-64years], [65+years], Christian, Muslim, Others, Single, Married, Widowed, Divorced from tmp2

  This is the data:

  CREATE TABLE [dbo].[TestTable](

  [RespondentName] [nvarchar](50) NULL,

  [PhoneNumber1] [nvarchar](50) NULL,

  [MaritalStatus] [nvarchar](50) NULL,

  [Region] [nvarchar](50) NULL,

  [Urbanisation] [nvarchar](50) NULL,

  [Religion] [nvarchar](50) NULL,

  [Gender] [nvarchar](10) NULL,

  [Location] [nvarchar](50) NULL,

  [AgeGroup] [nvarchar](50) NULL,

  [DayOfInterview] [nvarchar](50) NULL,

  [PhoneUsed] [nvarchar](50) NULL,

  [TabletsUsed] [nvarchar](50) NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'kome', N'07051758008', N'Single', N'South-South', N'Urban', N'Christian', N'Male', N'Lagos', N'18-24', N'Friday', N'Android Phone', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'joy Kofi', N'09099961220', N'Single', N'South-South', N'Urban', N'Christian', N'Female', N'Lagos', N'18-24', N'Saturday', N'Android Phone', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'taiwo akinwunmi', N'07056832797', N'Single', N'South-South', N'Urban', N'Christian', N'Male', N'Lagos', N'25-34', N'Saturday', N'BlackBerry', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'blessing', N'08182960051', N'Married', N'South-South', N'Urban', N'Christian', N'Female', N'Lagos', N'65+', N'Saturday', N'BlackBerry', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'blessing', N'08182960051', N'Married', N'South-South', N'Urban', N'Christian', N'Female', N'Lagos', N'65+', N'Saturday', N'Android Phone', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'Oyinlola Idowu', N'08109096652', N'Single', N'South-West', N'Semi-Urban', N'Christian', N'Female', N'Lagos', N'18-24', N'Saturday', N'Android Phone', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'Ibukun Olaoluwa', N'07051785755', N'Married', N'South-West', N'Urban', N'Christian', N'Female', N'Lagos', N'25-34', N'Sunday', N'BlackBerry', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'Ibukun Olaoluwa', N'07051785755', N'Married', N'South-West', N'Urban', N'Christian', N'Female', N'Lagos', N'25-34', N'Sunday', N'Android Phone', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'Philip Agbo', N'08086809359', N'Single', N'South-South', N'Semi-Urban', N'Christian', N'Male', N'Lagos', N'18-24', N'Sunday', N'nokia', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'ukpebor festus', N'08069148341', N'Single', N'South-South', N'Semi-Urban', N'Christian', N'Male', N'Lagos', N'8-11', N'Sunday', N'Regular Phone', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'Victoria Egemonu', N'07030213914', N'Widowed', N'South-South', N'Semi-Urban', N'Muslim', N'Male', N'Benin', N'8-11', N'Tuesday', N'TECNO', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'Victoria Egemonu', N'07030213914', N'Widowed', N'South-South', N'Semi-Urban', N'Muslim', N'Male', N'Benin', N'8-11', N'Tuesday', N'Tecno', N'None')

  INSERT [dbo].[TestTable] ([RespondentName], [PhoneNumber1], [MaritalStatus], [Region], [Urbanisation], [Religion], [Gender], [Location], [AgeGroup], [DayOfInterview], [PhoneUsed], [TabletsUsed]) VALUES (N'emmanuel', N'08091475363', N'Single', N'South-West', N'Urban', N'Christian', N'Male', N'Lagos', N'25-34', N'Friday', N'BlackBerry', N'Microsoft Tablet')

  I want the base to come as percentage of the initial criterial row, such as if i have some thing like this

  CriteriaBaseMonday

  Criteria130

  Semi-Urban50

  Urban80

  Instead of having the semi-urban and urban displaying raw values, i want to get percentages instead

  I hope you understand

  Thanks

  Tim

• timotech

  SSCrazy

  Points: 2698

  More actions

  April 23, 2016 at 6:34 am

  #1874086

  Ok guys,

  seems like i solved my problem myself

  This is the code that solves the problem:

  declare @Parameter nvarchar(4000)

  set @Parameter = 'Urbanisation'

  ;with tmp as (

  Select Count(*) as Base, DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar

  FROM TestTable

  group by DayOfInterview,Urbanisation, Gender, AgeGroup, Religion, MaritalStatus

  )

  ,

  tmp2 as (

  Select 'Criteria' as Criteria,

  SUM(Base) as Base,

  SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

  SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

  SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

  SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

  SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

  SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

  SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

  SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

  SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

  SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

  SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

  SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

  SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

  SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

  SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

  SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

  SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

  SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

  SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

  SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

  SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

  SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

  SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

  SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

  SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

  SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

  From tmp

  union all

  Select Criteria, round(Base * 100.0/(select sum(Base) from tmp),1), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday,Urban, [Semi-Urban], Male, Female, [8-11years], [12-17years], [18-24years], [25-34years], [35-44years], [45-54years], [55-64years], [65+years], Christian, Muslim, Others, Single, Married, Widowed, Divorced from (

  Select case when @Parameter = 'DayOfInterview' then DayOfInterview

  when @Parameter = 'Gender' then Gender

  when @Parameter = 'Urbanisation' then Urbanisation

  when @Parameter = 'AgeGroup' then AgeGroup

  when @Parameter = 'Religion' then Religion

  when @Parameter = 'MaritalStatus' then MaritalStatus

  End As Criteria,

  sum(Base) as Base,

  SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

  SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

  SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

  SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

  SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

  SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

  SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

  SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

  SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

  SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

  SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

  SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

  SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

  SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

  SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

  SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

  SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

  SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

  SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

  SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

  SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

  SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

  SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

  SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

  SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

  SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

  From tmp

  group by case when @Parameter = 'DayOfInterview' then DayOfInterview

  when @Parameter = 'Gender' then Gender

  when @Parameter = 'Urbanisation' then Urbanisation

  when @Parameter = 'AgeGroup' then AgeGroup

  when @Parameter = 'Religion' then Religion

  when @Parameter = 'MaritalStatus' then MaritalStatus

  End) as t

  )

  Select Criteria, Base, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday,Urban, [Semi-Urban], Male, Female, [8-11years], [12-17years], [18-24years], [25-34years], [35-44years], [45-54years], [55-64years], [65+years], Christian, Muslim, Others, Single, Married, Widowed, Divorced from tmp2

  Thanks for your inputs

  Tim

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 25, 2016 at 3:25 am

  #1874177

  Just one point. CTEs don't start with a ;.

  The semicolon is a row terminator. Your code should be

  declare @Parameter nvarchar(4000);

  set @Parameter = 'Urbanisation';

  With tmp as (

  Select Count(*) as Base, DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar

  FROM TestTable

  group by DayOfInterview,Urbanisation, Gender, AgeGroup, Religion, MaritalStatus

  )

  and then a row terminator at the end of the large query that I omitted.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• timotech

  SSCrazy

  Points: 2698

  More actions

  April 25, 2016 at 4:20 am

  #1874182

  GilaMonster (4/25/2016)

  ---

  Just one point. CTEs don't start with a ;.

  The semicolon is a row terminator. Your code should be

  declare @Parameter nvarchar(4000);

  set @Parameter = 'Urbanisation';

  With tmp as (

  Select Count(*) as Base, DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar

  FROM TestTable

  group by DayOfInterview,Urbanisation, Gender, AgeGroup, Religion, MaritalStatus

  )

  and then a row terminator at the end of the large query that I omitted.

  Ok, point taken

  Thanks