December 1, 2009 at 12:41 am
Hi all,
I have a table name: Feedback, under that following are the column:-
----------------------
ID | Qs1 | Qs2 | Qs3
----------------------
1 | 2 | 1 | 4
2 | 2 | 1 | 1
3 | 3 | 1 | 2
4 | 2 | 1 | 3
----------------------
Now I want calculate the feedback in following way:
No. of 2 in Qs1 is 3
No. of 3 in Qs1 is 1
& Total No. is 4
So the percentage for 2 is ((3/4)*100) & 3 is ((1/4)*100)
The same way I want to calculate for Qs2 & Qs3
I got the following query, but I am not able to calculate the percentage:
SELECT
(SELECT count(Qs1) FROM dbo.Feedback where Qs1 = '2'),
count(ID)
FROM dbo.Feedback
Hope I can make you understand my query!
December 1, 2009 at 1:17 am
Hi ,
You have posted in the Sql Server 7,2000 forum , are you really using an old version ?
It will make a big difference to the query.
December 1, 2009 at 1:20 am
Yes, am using MS SQL 2000
December 1, 2009 at 2:00 am
Untested but something like this...
Declare @TotCount
Select @Totcount = count(*)
from feedback
Select q1,
(@TotCount/100.0 ) * count(*)
from feedback
group by q1
December 1, 2009 at 2:07 am
No, it's not helping
December 1, 2009 at 2:20 am
mail2payan (12/1/2009)
No, it's not helping
In which way ,is it not helping ?
December 1, 2009 at 2:31 am
Hi,try this
create table #temp1
(
ID1 int,
Qs1 int,
Qs2 int,
Qs3 int
)
insert into #temp1
select 1,2,1,4
union all
select 2,2,1,1
union all
select 3,3,1,2
union all
select 4,2,1,3
select b.[char],b.[charcount],c.[totalrow],
(cast(b.[charcount] as float)/cast(c.[totalrow] as float))[percent]
from
(
select Qs1 [char],count(Qs1) [charcount]
from #temp1
group by Qs1
)b ,
(
select max(ID1)[totalrow] from #temp1
)c
December 1, 2009 at 2:31 am
Showing following errors:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.
Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@Totcount'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@TotCount'.
December 1, 2009 at 3:22 am
while i appreciate that you may be a newbie, it was a fairly obvious mistake on my part.
Did you try to fix it ?
Declare @TotCount integer
Select @Totcount = count(*)
from feedback
Select q1,
(@TotCount/100.0 ) * count(*)
from feedback
group by q1
December 1, 2009 at 10:09 pm
I tried like this with your reference:
SELECT
sum(case when Qs1='1' then 1 else 0 end)*100.0/count(ID) as no_of_1,
sum(case when Qs2='2' then 1 else 0 end)*100.0/count(ID) as no_of_2,
FROM dbo.feedback
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply