Solved

SQL calculate percentage in select statement.

Posted on 2009-05-17
4
1,883 Views
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:
-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?
0
Comment
Question by:kmerlo
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
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

0
 
LVL 4

Accepted Solution

by:
TimSledge earned 500 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
WHERE ID = 1

 
0
 
LVL 4

Expert Comment

by:TimSledge
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 
WHERE ID = 1

Open in new window

0
 

Author Closing Comment

by:kmerlo
ID: 31582462
That worked great! THanks for your help!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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