cumulative percentage

Question

Post reply

cumulative percentage

foxjazzG

SSCrazy

Points: 2221
More actions
July 25, 2012 at 5:03 pm

#278490

What I want to do is something like this:
SELECT unitssum, ProductSalesPopularityID, categoryid, ProductID, UnitsSold, rank1,
sum (unitssum) over (partition by ProductSalesPopularityID,categoryid order by unitssold desc) as percsum
FROM productsalesRanked order by productsalespopularityid,categoryid,unitssold desc
but I need to have a cumulative percentage (unitssum) is a percentage of the sum for that group.
Is there a way to consecutavely add the sum and start over for each partition (or group?)
code:
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[productsalesRanked] Script Date: 07/25/2012 16:59:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[productsalesRanked](
[rank1] [bigint] NULL,
[unitssum] [float] NULL,
[nvarchar](5) NULL,
[ProductSalesPopularityID] [int] NOT NULL,
[categoryid] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitsSold] [int] NULL
) ON [PRIMARY]
GO
113.5509396636993A582707891431415274
213.1058358061325B582707892975838265
313.0069238377844A582707891431410263
412.6112759643917A582707891445296255
512.6112759643917A582707892960786255
67.81404549950544C582707892975847158
75.98417408506429C582707891431417121
84.50049455984174D58270789170829491
93.41246290801187B58270789143141669
102.67062314540059A58270789297584054
111.68150346191889D58270789144529434
121.53313550939664NULL58270789220760831
131.38476755687438B58270789143141828
141.13748763600396B58270789144529223
150.939663699307616C58270789144529719
160.890207715133531NULL58270789297583918
170.791295746785361NULL58270789297584416
180.494559841740851D58270789220761010
190.494559841740851D58270789144529510
200.395647873392681NULL5827078930778568
210.346191889218595C5827078917082907
220.29673590504451NULL5827078914452986
230.0989119683481701D5827078929758422
240.0989119683481701NULL5827078929758462
250.0494559841740851NULL5827078914314141
260.0494559841740851NULL5827078922076091
270.0494559841740851D5827078917062871
280.0494559841740851NULL5827078929758411
29-0.0494559841740851D582707892975843-1
117.6112412177986C582707951445199376
216.5339578454333A582707951445197353
37.40046838407494B582707951708203158

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Are you looking for something like this:

CREATE TABLE [productsalesRanked](

[rank1] [bigint] NULL,

[unitssum] [float] NULL,

[nvarchar](5) NULL,

[ProductSalesPopularityID] [int] NOT NULL,

[categoryid] [int] NOT NULL,

[ProductID] [int] NOT NULL,

[UnitsSold] [int] NULL

);

INSERT INTO productsalesranked VALUES

(1,13.5509396636993,'A',58,270789,1431415,274),

(2,13.1058358061325,'B',58,270789,2975838,265),

(3,13.0069238377844,'A',58,270789,1431410,263),

(4,12.6112759643917,'A',58,270789,1445296,255),

(5,12.6112759643917,'A',58,270789,2960786,255),

(6,7.81404549950544,'C',58,270789,2975847,158),

(7,5.98417408506429,'C',58,270789,1431417,121),

(8,4.50049455984174,'D',58,270789,1708294,91),

(9,3.41246290801187,'B',58,270789,1431416,69),

(10,2.67062314540059,'A',58,270789,2975840,54),

(11,1.68150346191889,'D',58,270789,1445294,34),

(12,1.53313550939664,NULL,58,270789,2207608,31),

(13,1.38476755687438,'B',58,270789,1431418,28),

(14,1.13748763600396,'B',58,270789,1445292,23),

(15,0.939663699307616,'C',58,270789,1445297,19),

(16,0.890207715133531,NULL,58,270789,2975839,18),

(17,0.791295746785361,NULL,58,270789,2975844,16),

(18,0.494559841740851,'D',58,270789,2207610,10),

(19,0.494559841740851,'D',58,270789,1445295,10),

(20,0.395647873392681,NULL,58,270789,3077856,8),

(21,0.346191889218595,'C',58,270789,1708290,7),

(22,0.29673590504451,NULL,58,270789,1445298,6),

(23,0.0989119683481701,'D',58,270789,2975842,2),

(24,0.0989119683481701,NULL,58,270789,2975846,2),

(25,0.0494559841740851,NULL,58,270789,1431414,1),

(26,0.0494559841740851,NULL,58,270789,2207609,1),

(27,0.0494559841740851,'D',58,270789,1706287,1),

(28,0.0494559841740851,NULL,58,270789,2975841,1),

(29,-0.0494559841740851,'D',58,270789,2975843,-1),

(1,17.6112412177986,'C',58,270795,1445199,376),

(2,16.5339578454333,'A',58,270795,1445197,353),

(3,7.40046838407494,'B',58,270795,1708203,158);

SELECT p.ProductSalesPopularityID,

p.categoryid,

p.ProductID,

UnitsSold,

rank1,

CAST(unitssum * 100 / a.percsum AS DECIMAL(4,2)) AS CumPercSum,

a.PercSum

FROM productsalesranked p

JOIN

(

SELECT ProductSalesPopularityID,

categoryid,

ProductID,

SUM(unitssum) OVER

(PARTITION BY ProductSalesPopularityID,categoryid) AS percsum

FROM productsalesranked

) a

ON p.ProductSalesPopularityID = a.ProductSalesPopularityID

AND p.categoryid = a.categoryid

AND p.ProductID = a.ProductID

ORDER BY productsalespopularityid,categoryid,unitssold DESC;

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

foxjazzG SSCrazy Points: 2221 More actions · Answer 2

LokeshV I ran this on an empty db and get ambiguous column productsalespopularityid

I modded the query a bit taking the decimal cast out and just get what I had before in percentage.

what I would like is a cumulative sum where the output would like look

row 1 = 13.5509396636993

row 2 would be 13.5509396636993 + 13.1058358061325

row 3 would be 13.5509396636993 + 13.1058358061325 + 13.0069238377844

row 4 would be et.......

You get the idea.

Does anyone know how this can be done?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lokesh Vij (7/26/2012)
Are you looking for something like this:
CREATE TABLE [productsalesRanked](
[rank1] [bigint] NULL,
[unitssum] [float] NULL,
[nvarchar](5) NULL,
[ProductSalesPopularityID] [int] NOT NULL,
[categoryid] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitsSold] [int] NULL
);
INSERT INTO productsalesranked VALUES
(1,13.5509396636993,'A',58,270789,1431415,274),
(2,13.1058358061325,'B',58,270789,2975838,265),
(3,13.0069238377844,'A',58,270789,1431410,263),
(4,12.6112759643917,'A',58,270789,1445296,255),
(5,12.6112759643917,'A',58,270789,2960786,255),
(6,7.81404549950544,'C',58,270789,2975847,158),
(7,5.98417408506429,'C',58,270789,1431417,121),
(8,4.50049455984174,'D',58,270789,1708294,91),
(9,3.41246290801187,'B',58,270789,1431416,69),
(10,2.67062314540059,'A',58,270789,2975840,54),
(11,1.68150346191889,'D',58,270789,1445294,34),
(12,1.53313550939664,NULL,58,270789,2207608,31),
(13,1.38476755687438,'B',58,270789,1431418,28),
(14,1.13748763600396,'B',58,270789,1445292,23),
(15,0.939663699307616,'C',58,270789,1445297,19),
(16,0.890207715133531,NULL,58,270789,2975839,18),
(17,0.791295746785361,NULL,58,270789,2975844,16),
(18,0.494559841740851,'D',58,270789,2207610,10),
(19,0.494559841740851,'D',58,270789,1445295,10),
(20,0.395647873392681,NULL,58,270789,3077856,8),
(21,0.346191889218595,'C',58,270789,1708290,7),
(22,0.29673590504451,NULL,58,270789,1445298,6),
(23,0.0989119683481701,'D',58,270789,2975842,2),
(24,0.0989119683481701,NULL,58,270789,2975846,2),
(25,0.0494559841740851,NULL,58,270789,1431414,1),
(26,0.0494559841740851,NULL,58,270789,2207609,1),
(27,0.0494559841740851,'D',58,270789,1706287,1),
(28,0.0494559841740851,NULL,58,270789,2975841,1),
(29,-0.0494559841740851,'D',58,270789,2975843,-1),
(1,17.6112412177986,'C',58,270795,1445199,376),
(2,16.5339578454333,'A',58,270795,1445197,353),
(3,7.40046838407494,'B',58,270795,1708203,158);
SELECT p.ProductSalesPopularityID,
p.categoryid,
p.ProductID,
UnitsSold,
rank1,
CAST(unitssum * 100 / a.percsum AS DECIMAL(4,2)) AS CumPercSum,
a.PercSum
FROM productsalesranked p
JOIN
(
SELECT ProductSalesPopularityID,
categoryid,
ProductID,
SUM(unitssum) OVER
(PARTITION BY ProductSalesPopularityID,categoryid) AS percsum
FROM productsalesranked
) a
ON p.ProductSalesPopularityID = a.ProductSalesPopularityID
AND p.categoryid = a.categoryid
AND p.ProductID = a.ProductID
ORDER BY productsalespopularityid,categoryid,unitssold DESC;

You don't have any of the columns aliased in the ORDER BY clause. This is where FoxjazzG is getting the ambiguous column error.

...

ORDER BY productsalespopularityid,categoryid,unitssold DESC;