Percentage in select statement

I have a select statement with a division but it won't give me a percentage.  How can I format the results to give me a percentage or decimal format.


SELECT     PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, 
                      (R100_Error_A + R101_Error_A + R102_Error_A + R300_Error_A + R301_Error_A + R302_Error_A + R400_Error_A + R401_Error_A + R402_Error_A) 
                      / NPI_Counts AS LC_A, 
                      (R100_Error_E + R101_Error_E + R102_Error_E + R300_Error_E + R301_Error_E + R302_Error_E + R400_Error_E + R401_Error_E + R402_Error_E) 
                      / NPI_Counts AS LC_E
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month = MONTH(GETDATE()) - 1)

Open in new window

FairfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
SELECT     PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3,
                      (R100_Error_A + R101_Error_A + R102_Error_A + R300_Error_A + R301_Error_A + R302_Error_A + R400_Error_A + R401_Error_A + R402_Error_A)
                      / NPI_Counts AS LC_A * 1.0,
                      (R100_Error_E + R101_Error_E + R102_Error_E + R300_Error_E + R301_Error_E + R302_Error_E + R400_Error_E + R401_Error_E + R402_Error_E)
                      / NPI_Counts AS LC_E * 1.0
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month = MONTH(GETDATE()) - 1)
0
FairfieldAuthor Commented:
I am receiving an error:

Error in SELECT clause: expression near '*'.
Missing FROM clause.
Unable to parse query text.
0
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT     PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3,
                      (R100_Error_A + R101_Error_A + R102_Error_A + R300_Error_A + R301_Error_A + R302_Error_A + R400_Error_A + R401_Error_A + R402_Error_A)
                      / NPI_Counts  * 1.0 AS LC_A,
                      (R100_Error_E + R101_Error_E + R102_Error_E + R300_Error_E + R301_Error_E + R302_Error_E + R400_Error_E + R401_Error_E + R402_Error_E)
                      / NPI_Counts  * 1.0  AS LC_E
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month = MONTH(GETDATE()) - 1)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FairfieldAuthor Commented:
I am still not getting the correct results, is there a way to specify the number of decimals to 5 on the division statement and not a percentage?
0
Kevin CrossChief Technology OfficerCommented:
You need the * 1.0 in the numerator.
SELECT     PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, 
                      (R100_Error_A + R101_Error_A + R102_Error_A + R300_Error_A + R301_Error_A + R302_Error_A + R400_Error_A + R401_Error_A + R402_Error_A) * 1.0 / NPI_Counts AS LC_A, 
                      (R100_Error_E + R101_Error_E + R102_Error_E + R300_Error_E + R301_Error_E + R302_Error_E + R400_Error_E + R401_Error_E + R402_Error_E) * 1.0 / NPI_Counts AS LC_E
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month = MONTH(GETDATE()) - 1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
can u post some saple values and the expected results ?
0
FairfieldAuthor Commented:
Thanks, that worked!
0
Kevin CrossChief Technology OfficerCommented:
You can try this way.  I would also ensure that NPI_Counts can never be NULL or Zero then you will get a divide by zero error, but that is another subject.

I would use MONEY.  It only gives 4 decimal places, but usually that is all I want for percentage so when it converts (multiplied by 100) it is to two decimal precision.
SELECT     PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
, CAST(R100_Error_A + R101_Error_A + R102_Error_A + R300_Error_A + R301_Error_A + R302_Error_A + R400_Error_A + R401_Error_A + R402_Error_A AS MONEY) / NPI_Counts AS LC_A
, CAST(R100_Error_E + R101_Error_E + R102_Error_E + R300_Error_E + R301_Error_E + R302_Error_E + R400_Error_E + R401_Error_E + R402_Error_E AS MONEY) / NPI_Counts AS LC_E
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month = MONTH(GETDATE()) - 1)

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
If you need it to be 5 decimal places, then you will need to do the division then cast the result to datatype you want like so.

SELECT CAST(10 * 1.0 / 3 AS DECIMAL(10,5))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.