SQL calculate percentage in select statement.

Posted on 2009-05-17
Medium Priority
Last Modified: 2012-05-07
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:
-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?
Question by:kmerlo
  • 2
LVL 47

Expert Comment

ID: 24408581
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


Accepted Solution

TimSledge earned 2000 total points
ID: 24408586
When dividing you need to convert to a decimal data type.

SELECT convert(decimal(18,2), sum(q1)) /  convert(decimal(18,2), count(ID))
FROM DataWhy


Expert Comment

ID: 24408592
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 

Open in new window


Author Closing Comment

ID: 31582462
That worked great! THanks for your help!

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question