Solved

Show SQL Calculation as Percentage

Posted on 2007-11-23
8
1,170 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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20338140
Try 12 / 8.0
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 77
Date conversion in sql server 2012 6 26
SQL Server 2016 Developers Edition Install 4 59
SQL Query 2 0
Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

16 Experts available now in Live!

Get 1:1 Help Now