Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running Total % for a Pareto Chart (Using a CTE?) 1

Status
Not open for further replies.
kjv1611

kjv1611

New member
Jul 9, 2003
10,758
US
I'm trying to build a method to wrap up a couple of calculated columns to feed to Excel or possibly Reporting Services for building a chart. Excel is the current location of the chart, but it doesn't have to remain that way.

First, here's a link that contains an idea of how I'd like to do it if I can sort it out and understand it well enough:

The post where the responder uses a CTE is where the post starts, "While Sam Saffron did great work on it, he still didn't provide..". That post is by Roman Pekar on Dec 6 '12 at 13:23.

So if I can figure out how to get that to work at all, that'd be great, I think. In the test, I got no results until I removed the "where T.ord = 0" from the CTE, so that doesn't help my understanding for certain.

It baffles me that you can refer to a CTE from within a CTE??? Just seems impossible!

My scenario where I want to do something like this... at least I THINK it's something like this, is here:
[CODE SQL]IF OBJECT_ID('tempdb..#Orchard') IS NOT NULL DROP TABLE #Orchard

CREATE TABLE #Orchard (FruitID int NOT NULL IDENTITY(1,1) ,Fruit varchar(20) , Variation varchar(20) ,Color varchar(20) ,Pounds decimal(5,2))
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15
INSERT INTO #Orchard SELECT 'Apple' ,'Red Delicious' ,'Red' ,0.4
INSERT INTO #Orchard SELECT 'Apple' ,'Granny Smith' ,'Green' ,0.4
INSERT INTO #Orchard SELECT 'Orange' ,'Navel Orange' ,'Orange' ,0.7
INSERT INTO #Orchard SELECT 'Orange' ,'Seeded Orange' ,'Orange' ,0.9
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seedless' ,'Green' ,4
INSERT INTO #Orchard SELECT 'Watermelon' ,'Moon and Stars' ,'Green' ,2
INSERT INTO #Orchard SELECT 'Watermelon' ,'Seeded' ,'Green' ,11
INSERT INTO #Orchard SELECT 'Banana' ,'Banana' ,'Yellow' ,.15

;WITH FruitBasket AS
(
SELECT Fruit
, SUM(CASE WHEN Color = 'Red' THEN 1 ELSE 0 END) AS RedFruit
, SUM(CASE WHEN Color = 'Green' THEN 1 ELSE 0 END) AS GreenFruit
, CAST(COUNT(1) AS decimal(10,4)) AS TotalFruit
FROM #Orchard
GROUP BY Fruit
)

, OrderedBasket AS
(
SELECT Fruit ,RedFruit ,GreenFruit ,TotalFruit
FROM FruitBasket
[HIGHLIGHT]-- ORDER BY TotalFruit DESC[/HIGHLIGHT]
)

SELECT Fruit
, RedFruit
, GreenFruit
, TotalFruit
[HIGHLIGHT]-- , RunningTotalOfGreenFruit AS CumulativeTotalGreen[/HIGHLIGHT]
[HIGHLIGHT]-- , RunningTotalOfGreenFruit / TotalFruit AS PctCumulativeGreen[/HIGHLIGHT]
FROM FruitBasket
[/CODE]

So the steps I think I need to take are:
Order by the most common item (color in this instance) down to the least (so sort in DESC order)
Capture the cumulative total up to that row (Descending value order) so that now at Row 2, the total is higher, and therefore the percentage is higher than Row 1
Capture the cumulative GreenFruit Count
Divide the cumulative GreenFruit Count by the total

I probably am still not fully explaining it, so let me demonstrate with a list of values and example formulas we are using in Excel to do this:

Values:
Code: 
ColA		B		C		D		                E
Fruit		Green		Red		Cumulative Total		% Green
Apple		12		12		40%		                50%
Banana		0		0		40%		                0%
Orange		0		0		40%		                0%
Watermelon	36		0		100%		                100%

So, assuming I'm starting in cell A1, here are some example formulas:
D2 =(C2+B2)/(SUM($B$2:$B$5)+SUM($C$2:$C$5))
D3 =D2+(C3+B3)/(SUM($B$2:$B$5)+SUM($C$2:$C$5))
D4 =D3+(C4+B4)/(SUM($B$2:$B$5)+SUM($C$2:$C$5))
E5 =IF(C2+B2=0,0,B2/(C2+B2))

So, D4 is basically same as D3, formula-wise, it's just copied down to the next row, and so on..
The formulas are pretty basic in E5, and I don't think that will require much effort to get that piece, just basic addition and division.

Thanks for any thoughts/suggestions/references

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
Sort by date Sort by votes
First, your example doesn't match what you say you want, so I'm guessing the example is correct.

I've had my head buried in the windowing functions and I think that's a good thing for you. For the running total of green fruit, you can use:

Code: 
, SUM(GreenFruit) OVER (ORDER BY GreenFruit RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GreenRunningTotal

I just don't understand what else you're looking for well enough to answer, but if you can clarify, I can try. Whatever it is, it's likely you want to use over variations with OVER.

Tamar


 
Upvote 0 Downvote
Hmm, thanks. I have used ROW_NUMBER() OVER (PARTITION BY.. ORDER BY..) many times, but didn't think of that for this sort of situation. I'll see what I can come up with later today or tomorrow if not time today..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
Upvote 0 Downvote
Keep in mind that you can create expressions using OVER. That is, you could, for example, use OVER once to compute a subtotal, and again to compute a grand total and divide one by the other for a percentage, something like this:

SUM(blah) OVER (...) / SUM(blah) OVER (something larger)

Depending on the case, you may want to wrap the whole thing in CAST() to get the right result type, and/or multiply by 100 to get a percentage.

Tamar
 
Upvote 0 Downvote
True - good idea. I've mainly used similar for creating a better way to sort, but yeah, that does sound like a winning idea in this situation. Hopefully I'll get time soon to get back to that project and get that worked out. Thanks again.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
302
Andrzejek
Andrzejek
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
235
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
172
grnzbra
grnzbra
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
362
gmmastros
gmmastros
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
371
Volvere
V

Part and Inventory Search

Sponsor

Back
Top