CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
precose
precose

Reputation: 141

Finding each row's percentage of total

SELECT I.[Order Number], SUM(CAST([Ext Price] AS FLOAT)) AS Rev 
        FROM stg.Invoices I 
        where i.[order number] = '64100097'
        GROUP BY I.[Order Number]

Gives me:

Order Number    Rev
64100097    124079.33

When I try to get the % of revenue per line using:

SELECT StgID,
CAST((CAST([Ext Price] AS FLOAT)) / (NULLIF(Rev,0)) AS FLOAT)
FROM stg.Invoices I
JOIN (
        SELECT I.[Order Number], 
        SUM(CAST([Ext Price] AS FLOAT)) AS Rev 
        FROM stg.Invoices I 
        where i.[order number] = '64100097'
        GROUP BY I.[Order Number]
) as TotRev on I.[Order Number] = TotRev.[Order Number]

I get this:

StgID   RevPct
7006418 0.202934687026437
7006427 0.000460995397057673
7006417 0.0274442971282969
7006426 0.000415379418957211
7006422 0.0312703171430729
7006425 0.000609287622684616
7006424 0.0161187201768417
7006420 0.197365669205338
7006419 0.226870986489047
7006428 0.000134752500678397
7006421 0.10299056256993
7006423 0.193384345321658

Which totals up to: .08333333333

Shouldn't this all add up to 1?

I'm not understanding why I can't get each line to add up to a total percentage of 100%.

Here is proof that all of them should add up to the total:

select stgid, [ext price]
from stg.Invoices
where [order number] = '64100097'


stgid   ext price
7006420 24489
7006418 25180
7006427 57.2
7006417 3405.27
7006426 51.54
7006421 12779
7006423 23995
7006422 3880
7006425 75.6
7006424 2000
7006419 28150
7006428 16.72

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use window functions:

SELECT I.[Order Number], SUM(CAST([Ext Price] AS FLOAT)) AS Rev ,
       SUM(CAST([Ext Price] AS FLOAT)) / NULLIF(SUM(SUM(CAST([Ext Price] AS FLOAT))) OVER (), 0) as p_rev
FROM stg.Invoices I 
WHERE i.[order number] = '64100097'
GROUP BY I.[Order Number];

Upvotes: 1

Related Questions