Calculating the percentage of product revenues for reporting

Question

Post reply

Calculating the percentage of product revenues for reporting

f9c882q 61644

SSChasing Mays

Points: 616
More actions
March 2, 2012 at 2:56 pm

#257816

I have to calculate the percentage of revenue for each of the products indicated in the code below. Unfortunately when I execute the code below I get zero. Any help will be appreciated.
SELECT ((A.TRADE/A.TOTAL)*100) AS Trade,((A.CREDIT/A.TOTAL)*100) AS Credit,((A.OTHER/A.TOTAL)*100) AS Other
from(
Select SUM(Trade) as Trade, SUM(Credit) AS Credit, SUM(Others) AS Other, SUM(Trade+Credit+Others) as Total
From dbo.GL_FINANCE_FINAL_TABLE) A
ORDER BY ((A.TRADE/A.TOTAL)*100)
Results:
Trade Credit Other
0 0 0

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Replace A.TOTAL with A.TOTAL*1.00 to avoid an integer divsion (usually resulting in a zero value in such a scenario).

Example:

SELECT 1/2*100, 1/(2*1.00)*100

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 2

Sorry, post withdrawn...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 3

LutzM (3/2/2012)
Replace A.TOTAL with A.TOTAL*1.00 to avoid an integer divsion (usually resulting in a zero value in such a scenario).
Example:
SELECT 1/2*100, 1/(2*1.00)*100

Just an idea, Lutz. Move the *100 and give it the decimal place so you only need to do 1 multiplication instead of 2.

SELECT 1*100.0/2, 100.0*1/2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)