February 18, 2011 at 4:43 am
So I have a table called satisfaction with 5 columns
• sati_satiid
• sati_quickly
• sati_informed
• sati_lettersunderstand
• sati_recommend
My problem is I’ve been asked to produce a report using SSRS to provide a total count on the number of records within a given period (this is easy) and provide an average response on the results. I can do this by simply converting the data as int using the below query. I am struggling to convert it back and display the avg result in text format.
Any help or advice would be appreciated
select (COUNT(sati_satiid))as 'Total number of surveys',
avg(CASE WHEN sati_quickly ='quickly' THEN 1 ELSE 0 END) as 'CC dealt quickly with their complaint',
avg ( CASE WHEN sati_informed ='y' THEN 1 ELSE 0 END ) as 'CC kept them informed',
avg( CASE WHEN sati_lettersunderstand ='y' THEN 1 ELSE 0 END ) as 'CCs letters easy to understand',
avg( CASE WHEN sati_recommend ='y' THEN 1 ELSE 1 END ) as 'Would recommend CC'
from Satisfaction
February 18, 2011 at 5:49 am
Hi,
DECLARE @Table TABLE (Col varchar(20),Col1 int,Col2 numeric(10,2))
Insert into @Table
Values ('1',1,1),('2',2,2),('3',3,3),('4',4,4),('5',5,5)
Select * from @Table
Select AVG(Col1) from @Table
Select AVG(Col2) from @Table
Select AVG(Col2) from @Table --- must fail since it varchar data type
Operand data type varchar is invalid for avg operator
It will do operations on only
tinyint
smallint
int
bigint
decimal category (p, s)
money and smallmoney category
float and real category
Thanks
Parthi
February 18, 2011 at 10:45 am
Hopefully I understand what you're trying to do here. I made a test scenario:
create table satisfaction(
sati_satiid smallint,
sati_quickly char(1),
sati_informed char(1),
sati_lettersunderstand char(1),
sati_recommend char(1)
)
go
declare @i smallint
set @i = 1
while @i <= 100
begin
insert into satisfaction select @i,
case when cast(20*RAND() as int)%2 = 1 then 'y' else 'n' end,
case when cast(20*RAND() as int)%2 = 1 then 'y' else 'n' end,
case when cast(20*RAND() as int)%2 = 1 then 'y' else 'n' end,
case when cast(20*RAND() as int)%2 = 1 then 'y' else 'n' end
set @i = @i+1
end
go
To show you my data distribution:
select (COUNT(sati_satiid))as 'Total number of surveys',
sum( CASE WHEN sati_quickly ='y' THEN 1 ELSE 0 END) as 'CC dealt quickly with their complaint',
sum( CASE WHEN sati_informed ='y' THEN 1 ELSE 0 END ) as 'CC kept them informed',
sum( CASE WHEN sati_lettersunderstand ='y' THEN 1 ELSE 0 END ) as 'CCs letters easy to understand',
sum( CASE WHEN sati_recommend ='y' THEN 1 ELSE 0 END ) as 'Would recommend CC'
from Satisfaction
Results:
100 42 47 51 46
So here's the query to get the average response:
select (COUNT(sati_satiid))as 'Total number of surveys',
case(round(avg(CASE WHEN sati_quickly ='y' THEN 1.0 ELSE 0.0 END), 0)) when 1 then 'y' else 'n' end as 'CC dealt quickly with their complaint',
case(round(avg ( CASE WHEN sati_informed ='y' THEN 1.0 ELSE 0.0 END ), 0)) when 1 then 'y' else 'n' end as 'CC kept them informed',
case(round(avg( CASE WHEN sati_lettersunderstand ='y' THEN 1.0 ELSE 0.0 END ), 0)) when 1 then 'y' else 'n' end as 'CCs letters easy to understand',
case(round(avg( CASE WHEN sati_recommend ='y' THEN 1.0 ELSE 0.0 END ), 0)) when 1 then 'y' else 'n' end as 'Would recommend CC'
from Satisfaction
Results:
100 n n y n
February 21, 2011 at 2:21 am
Thanks that works .
Celko, we can’t all be great at statistics I’m sure even you had to start somewhere, thanks for the book refs though Ill look them up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply