CODEWITHSUNDEEP
sqlsql-servert-sql
Jeff
Jeff

Reputation: 449

Divide a single row by the entire columns sum

I need to divide a single rows value (units in my case) by the entire rows sum to get a percent (the units column). Can someone help me with this? Below is the code and the data result for the code. 

    select db.ManufAbbrName Manufacturer, 
       db.ProdName ProductName,
       sum(wk.Units) Units,
       wk.RecordDate RecordDate
  from MDDB db
  join wkdata wk 
    on db.NDC = wk.NDC11 
  join @NDCs nd
    on nd.NDCs = wk.NDC11
 where wk.RecordDate between @StartDate and @EndDate
group by db.ManufAbbrName,
         db.ProdName,
         wk.RecordDate

Results set:

Example1    CLOBETASOL PROPIONATE EMO   264475  2013-11-01 00:00:00.000
Example2    CLOBETASOL PROPIONATE       187371  2013-11-01 00:00:00.000
Example3    OLUX-E                      82154   2013-11-01 00:00:00.000

So I need to get the percentage values of column 3 for each of the manufacutrers in row 1. So for the first one, 264475 / 534000

Upvotes: 1

Views: 380

Answers (2)

sebby
sebby

Reputation: 1

What you are looking for is the SUM(y) OVER(Partition By x) usage. You could do the following:

SELECT group_id
, value
, SUM(value) OVER(PARTITION BY group_id)
, CASE WHEN SUM(value) OVER(PARTITION BY group_id) = 0 THEN 0 
ELSE (1.0*value) / (1.0*SUM(value) OVER(PARTITION BY group_id)) END as Rate

FROM t

**SQLFiddle code

Upvotes: 0

T I
T I

Reputation: 9933

Look into the OVER clause. Here is an article about it

A small example

DECLARE @t TABLE (group_id CHAR(1), value INT)

INSERT INTO @t (group_id, value)
VALUES ('A', 5),('A', 5), ('A', 5),('B', 5),('B', 5)

SELECT group_id, value, group_total, rate = CASE WHEN group_total = 0 THEN 0 ELSE (1.0*value) / (1.0*group_total) END
FROM (
    SELECT group_id, value, group_total = SUM(value) OVER(PARTITION BY group_id)
    FROM @t
) t

Upvotes: 1

Related Questions