[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# SQL calculate percentage in select statement.

Posted on 2009-05-17
Medium Priority
1,910 Views
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
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
• 2

LVL 46

Expert Comment

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

LVL 4

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

0

LVL 4

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
WHERE ID = 1
``````
0

Author Closing Comment

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

## Featured Post

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
###### Suggested Courses
Course of the Month12 days, 13 hours left to enroll