Nested SQL Query - % of Group Total

Question

Nested SQL Query - % of Group Total

Viewing 15 posts - 16 through 30 (of 30 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mjbkm (2/7/2013)
ChrisM@Work (2/7/2013)
Aggregate Amount by Group and type, then feed the result into Sean's original query.
That is where I was stuck before. How would I change this?
SELECT
[Group],
[Type],
Amount,
GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])
FROM SampleData

Use GROUP BY and SUM.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mjbkm Ten Centuries Points: 1104 More actions · Answer 2

Got it!!!!!!!!

WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT 'Cat', 'Typeb', 20

)

SELECT

d.[Group],

d.[Type],

d.Amount,

x.Percentage

FROM (

SELECT

[Group],

[Type],

SUM(Amount)AS Amount,

GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])

FROM SampleData

GROUP BY [Group],[Type],Amount

) d

CROSS APPLY (SELECT Percentage = CAST(d.Amount*1.00/d.GroupTotal AS NUMERIC(5,2))) x

ORDER BY d.[Group] DESC, x.Percentage

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

ChrisM@Work (2/7/2013)
Actually I misread your post Sean - thought you had used the "aggregate in a derived table" trick, or I wouldn't have posted this!

I am not sure if I know that that trick is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mjbkm (2/7/2013)
Got it!!!!!!!!
...

Not quite...

WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT 'Cat', 'Typeb', 20

)

SELECT

*

FROM (

SELECT

d.[Group],

d.[Type],

d.Amount,

GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])

FROM (

SELECT

[Group],

[Type],

Amount = SUM(Amount)

FROM SampleData

GROUP BY [Group],[Type]--,Amount

) d

) q

CROSS APPLY (SELECT Percentage = CAST(q.Amount*1.00/q.GroupTotal AS NUMERIC(5,2))) x

ORDER BY q.[Group] DESC, x.Percentage

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Sean Lange (2/7/2013)
ChrisM@Work (2/7/2013)
Actually I misread your post Sean - thought you had used the "aggregate in a derived table" trick, or I wouldn't have posted this!
I am not sure if I know that that trick is.

I'm not sure if I explained it very well!

FROM (

SELECT

[Group],

[Type],

Amount = SUM(Amount)

FROM SampleData

GROUP BY [Group],[Type]--,Amount

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mjbkm Ten Centuries Points: 1104 More actions · Answer 6

OK, I was closer.

Also, I applied this to my live query and I got this error:

Divide by zero error encountered.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

ChrisM@Work (2/7/2013)
Sean Lange (2/7/2013)
ChrisM@Work (2/7/2013)
Actually I misread your post Sean - thought you had used the "aggregate in a derived table" trick, or I wouldn't have posted this!
I am not sure if I know that that trick is.
I'm not sure if I explained it very well!
FROM (
SELECT
[Group],
[Type],
Amount = SUM(Amount)
FROM SampleData
GROUP BY [Group],[Type]--,Amount
) d

Ahh gotcha.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

mjbkm (2/7/2013)
OK, I was closer.
Also, I applied this to my live query and I got this error:
Divide by zero error encountered.

-- replace this

SELECT Percentage = CAST(q.Amount*1.00/q.GroupTotal AS NUMERIC(5,2))

-- with this

SELECT Percentage = CAST(q.Amount*1.00/NULLIF(q.GroupTotal,0) AS NUMERIC(5,2))

Edit: added missing parameter in NULLIF

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mjbkm Ten Centuries Points: 1104 More actions · Answer 9

Perfect... so far so good. Now just to test to make sure all of my numbers are coming out correct. Thank you again! This site is awesome!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Here's another option using window aggregate functions:

;WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT 'Cat', 'Typeb', 20

)

SELECT [Group], [Type]

,[Amount]

,[GroupTotal]

,[Percentage]=CAST((1.*[Amount])/

CASE WHEN [GroupTotal] <> 0 THEN 1.*[GroupTotal] ELSE 1. END AS DECIMAL(5,2))

FROM (

SELECT [Group], [Type], [Amount]=SUM([Amount])

,[GroupTotal]=SUM(SUM([Amount])) OVER (PARTITION BY [Group])

FROM SampleData

GROUP BY [Group], [Type]) a

Possibly not as swift as ChrisM's solution but pretty succinct.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

mjbkm Ten Centuries Points: 1104 More actions · Answer 11

mjbkm

Ten Centuries

Points: 1104

February 8, 2013 at 3:12 am

#1585824

Thank you!! I will try this too.

mjbkm Ten Centuries Points: 1104 More actions · Answer 12

I need to figure out one last solution with this. Is it possible to do the PIVOT SQL function with this solution?

(http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx)

such as:

Group as the rows (Dog, Cat)

Type as the columns (Typea, Typeb, Typec)

Thank you either way.

ChrisM@Work (2/7/2013)
mjbkm (2/7/2013)
OK, I was closer.
Also, I applied this to my live query and I got this error:
Divide by zero error encountered.
-- replace this
SELECT Percentage = CAST(q.Amount*1.00/q.GroupTotal AS NUMERIC(5,2))
-- with this
SELECT Percentage = CAST(q.Amount*1.00/NULLIF(q.GroupTotal,0) AS NUMERIC(5,2))
Edit: added missing parameter in NULLIF

mjbkm Ten Centuries Points: 1104 More actions · Answer 13

Here is the last complete I was working with:

WITH SampleData ([Group], [Type], Amount) AS (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT 'Cat', 'Typeb', 20

)

SELECT

*

FROM (

SELECT

d.[Group],

d.[Type],

d.Amount,

GroupTotal = SUM(Amount) OVER(PARTITION BY [Group])

FROM (

SELECT

[Group],

[Type],

Amount = SUM(Amount)

FROM SampleData

GROUP BY [Group],[Type]--,Amount

) d

) q

CROSS APPLY (SELECT Percentage = CAST(q.Amount*1.00/NULLIF(q.GroupTotal,0)AS NUMERIC(5,2))) x

ORDER BY q.[Group] DESC, x.Percentage

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 14

What the heck... let's go whole hawg and anticipate the next questions, as well, which will probably be...

1. Can we pivot the amounts, as well?

2. Can we get a total of the amounts for each row?

3. Any chance of getting a "Total" row?

4. Can we fix the "percent" columns so they really are percents instead of just a ratio?

Here's the test data in a test table...

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Build and populate the test table on-the-fly.

SELECT [Group], [Type], Amount

INTO #TestTable

FROM (

SELECT 'Dog', 'Typea', 1 UNION ALL

SELECT 'Dog', 'Typeb', 2 UNION ALL

SELECT 'Dog', 'Typec', 3 UNION ALL

SELECT 'Cat', 'Typea', 10 UNION ALL

SELECT 'Cat', 'Typeb', 20) d ([Group], [Type], Amount)

;

Here's the code to solve all the requirements whether old, new, and anticipated...

WITH

ctePreAgg AS

( --=== Preaggegate and do the pivot

SELECT [Group],

TypeA_Amt = SUM(CASE WHEN [Type] = 'Typea' THEN Amount ELSE 0 END),

TypeB_Amt = SUM(CASE WHEN [Type] = 'Typeb' THEN Amount ELSE 0 END),

TypeC_Amt = SUM(CASE WHEN [Type] = 'Typec' THEN Amount ELSE 0 END),

Total_Amt = SUM(Amount)

FROM #TestTable

GROUP BY [Group]

) --=== Do the final aggregation along with some special handling for "totaling" percents.

-- This also does a ROLLUP to produce the "Total" line.

SELECT [Group] = CASE WHEN GROUPING([GROUP]) = 0 THEN [Group] ELSE 'Total' END,

TypeA_Amt = SUM(TypeA_Amt),

TypeA_Pct = SUM((TypeA_Amt*100.0)/Total_Amt)

/ CASE WHEN GROUPING([Group]) =1 THEN COUNT(*) ELSE 1 END,

TypeB_Amt = SUM(TypeB_Amt),

TypeB_Pct = SUM((TypeB_Amt*100.0)/Total_Amt)

/ CASE WHEN GROUPING([Group]) =1 THEN COUNT(*) ELSE 1 END,

TypeC_Amt = SUM(TypeC_Amt),

TypeC_Pct = SUM((TypeC_Amt*100.0)/Total_Amt)

/ CASE WHEN GROUPING([Group]) =1 THEN COUNT(*) ELSE 1 END,

Total_Amt = SUM(Total_Amt)

FROM ctePreAgg

GROUP BY [Group] WITH ROLLUP

;

Here's the output. If you want 2 places on the percentages, please feel free to add a CAST where appropriate.

Group TypeA_Amt TypeA_Pct TypeB_Amt TypeB_Pct TypeC_Amt TypeC_Pct Total_Amt

----- --------- --------------- --------- --------------- --------- --------------- ---------

Cat 40 66.666666666666 20 33.333333333333 0 0.000000000000 60

Dog 1 16.666666666666 2 33.333333333333 3 50.000000000000 6

Total 41 41.666666666666 22 33.333333333333 3 25.000000000000 66

If you want to know more about "Converting Rows to Columns", please see the following articles.

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

--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)

mjbkm Ten Centuries Points: 1104 More actions · Answer 15

Ha, you are correct. I just ended up keeping the previous as is and created all new. And entire switchboard of different results. However, this is awesome. I will pull it in and try it out too.

Thank you!!