Solved

SQL calculate percentage in select statement.

Posted on 2009-05-17
4
1,878 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now