Solved

SQL calculate percentage in select statement.

Posted on 2009-05-17
4
1,892 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

729 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