Solved

Show SQL Calculation as Percentage

Posted on 2007-11-23
8
1,172 Views
Last Modified: 2010-04-21
I have this code

(dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 80 AS sPercent

and when I run it it  sPercent shows as 0.

This is because the actual calcualtion is    12 / 80 = 0.15


so SQL rounds it off to 0


What I want to achieve is for sPercent to show 15%  


Can anyone advise me on this?

Thanks

SELECT     dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3 AS total_score, 
                      (dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 12 AS sPercent, 
                      dbo.vwSurveyCount.CompletedSurveys
FROM         dbo.tblBTLSurvey CROSS JOIN
                      dbo.vwSurveyCount

Open in new window

0
Comment
Question by:edjones1
8 Comments
 
LVL 23

Assisted Solution

by:Ashish Patel
Ashish Patel earned 300 total points
ID: 20338133
Use this

(dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 80.00 AS sPercent
0
 
LVL 23

Assisted Solution

by:Ashish Patel
Ashish Patel earned 300 total points
ID: 20338136
I mean for 15 %
just do this
(dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 80*100 AS sPercent
Or or 15.00 %
(dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 80.00*100.00 AS sPercent


0
 
LVL 23

Assisted Solution

by:Ashish Patel
Ashish Patel earned 300 total points
ID: 20338138
Your code below.
SELECT     dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3 AS total_score, 
                      (dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 12 * 100 AS sPercent, 
                      dbo.vwSurveyCount.CompletedSurveys
FROM         dbo.tblBTLSurvey CROSS JOIN
                      dbo.vwSurveyCount

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20338140
Try 12 / 8.0
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20338166
Hi,

/ (Divide) operator in Books online:

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

 
SELECT     dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3 AS total_score, 
                    (dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) / 0.12 AS sPercent, 
                      dbo.vwSurveyCount.CompletedSurveys
FROM         dbo.tblBTLSurvey CROSS JOIN
                      dbo.vwSurveyCount

Open in new window

0
 
LVL 4

Accepted Solution

by:
jindalankush earned 200 total points
ID: 20338256
during calculation convert any field  in float format
0
 

Author Comment

by:edjones1
ID: 20338305

The answer was


SELECT     dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3 AS total_score, 
                      CAST(CONVERT(decimal(18, 2), dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3) 
                      / 80 AS varchar) + '%' AS sPercent, dbo.vwSurveyCount.CompletedSurveys
FROM         dbo.tblBTLSurvey CROSS JOIN
                      dbo.vwSurveyCount

Open in new window

0
 

Author Closing Comment

by:edjones1
ID: 31410648
solution

SELECT     dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3 AS total_score,
                      CAST(CONVERT(decimal(18, 2), dbo.tblBTLSurvey.NewBusinessQ1 + dbo.tblBTLSurvey.NewBusinessQ2 + dbo.tblBTLSurvey.NewBusinessQ3)
                      / 80AS varchar) + '%' AS sPercent, dbo.vwSurveyCount.CompletedSurveys
FROM         dbo.tblBTLSurvey CROSS JOIN
                      dbo.vwSurveyCount
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 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