SQL calculate percentage in select statement.
Posted on 2009-05-17
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:
-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?