asked on

SQL calculate percentage in select statement.

I have a table that is set up like so, called DataWhy:

ID Q1 Q2 Q3 Q4 Q5
1 1 0 0 1 1
1 0 1 0 1 0
4 0 1 1 0 1

It stores the results of a survey where users check boxes on a web page for 5 different questions. A '1' is entered for each question where the box is checked. The ID column itentifies the item they are answering questions about.

I am populating an ASP.net gridview that contains these columns:
-Question
-Qustion sum
-Score percentage (question sum / number of completed surveys for the item)

I have tried a number of different ways, and in every case the percentage is returned as zero. This is the last query I tried:

SELECT 'The price/opening bid is too high.' Question, SUM(q1) Score, (SELECT
sum(q1)/count(ID) FROM DataWhy WHERE ID = 1) ScorePercent from datawhy where ID = 1

What I would like to return (for id = 1, for example) is:

Question Score ScorePercent
The price/opening bid is too high. 1 .5

Again, everything works, except the percentage always comes up zero. Is there even a wayto do this?

aikimark

how about this?

SELECT 'The price/opening bid is too high.' Question, SUM(q1) Score, SUM(q1)/(SELECT count(ID) FROM DataWhy WHERE ID = 1) ScorePercent from datawhy where ID = 1

Open in new window

ASKER CERTIFIED SOLUTION

TimSledge

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TimSledge

Run the script below and you will get a the result .5000...

create table dbo.tblDataWhy(ID int, Q1 int)
 
insert into dbo.tblDataWhy (ID, Q1) select 1, 1
insert into dbo.tblDataWhy (ID, Q1) select 1, 0
 
 
SELECT convert(decimal(18,2), sum(q1)) /  convert(decimal(18,2), count(ID)) 
FROM dbo.tblDataWhy 
WHERE ID = 1

Open in new window

kmerlo

ASKER

That worked great! THanks for your help!