August 19, 2011 at 8:47 am
Is there an easy way to grab the total for a calculation within a CTE?
WITH MYCTE AS (
SELECT MYSTUFF count_big(*) AS [Total Stuff]
, sum(cast(size_in_bytes as decimal(18,2))) AS [Total Size]
, avg(Stuff) AS [Avg Stuff]
, sum(cast((CASE WHEN MYSTUFF = 1 THEN OTHERSTUFF ELSE 0 END) as decimal(18,2))) AS [OTHERSTUFF]
, sum(CASE WHEN DIFFSTUFF = 1 THEN 1 ELSE 0 END) AS [Total DIFF],
FROM MY_TABLE_OF_STUFF
GROUP BY STUFF With Rollup
)
SELECT [MYSTUFF]
[Total Stuff],
[Total Size],
[OTHERSTUFF],
[Total DIFF]
[MYSTUFF]/[TOTAL STUFF * 100 AS [GRAND TOTAL %] /*-->Where do I get the NON-GROUPED TOTAL*/
FROM MYCTE
Should I use a UNION for the Grand Total?, or, is there a way to grab the "WITH ROLLUP" value and divide by that? Confused on how to easily do this. TIA
Hope my example makes sense!
August 19, 2011 at 10:28 am
No takers?
August 19, 2011 at 2:04 pm
Why don't get your grand total by itself first in a separate query? That seems like the easiest way.
select @GrandTotal = sum(MyStuff)
then your query.
[MYSTUFF] / @GrandTotal * 100 AS [GRAND TOTAL %]
_______________________________________________________________
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/
August 19, 2011 at 2:08 pm
Thanks, Sean! That is a valid solution I thought of. I wanted a single query pass. I essentially figured it out using OVER(). I was able to use the windowed function to select values NOT in the Group BY
Like this:
WITH MYCTE AS (
SELECT MYSTUFF count_big(*) AS [Total Stuff]
, sum(cast(size_in_bytes as decimal(18,2))) AS [Total Size]
, avg(Stuff) AS [Avg Stuff]
, sum(cast((CASE WHEN MYSTUFF = 1 THEN OTHERSTUFF ELSE 0 END) as decimal(18,2))) AS [OTHERSTUFF]
, sum(CASE WHEN DIFFSTUFF = 1 THEN 1 ELSE 0 END) AS [Total DIFF],
FROM MY_TABLE_OF_STUFF
GROUP BY STUFF With Rollup
)
SELECT [MYSTUFF]
[Total Stuff],
[Total Size],
[OTHERSTUFF],
[Total DIFF]
Cast([MYSTUFF]*1.0/Sum([TOTAL STUFF ])OVER() * 100.0 AS DECIMAL(5, 2)) As MYSTUFFPCT
FROM MYCTE
August 19, 2011 at 2:09 pm
As is typical around here can you share you solution so others who search your question later can find your resolution? Glad you solved it.
_______________________________________________________________
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/
August 19, 2011 at 2:11 pm
I posted, and then edited to cut and paste - you're too quick! See my solution in the previous thread.
August 19, 2011 at 2:12 pm
LOL. :hehe:
_______________________________________________________________
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/
August 20, 2011 at 12:10 pm
I don't know how, but I've started to love J.Celko replies...
They actually make my day, they fill it with laughter and light. 😀
I wonder, does he really remember all these ISO numbers? Do his replies help him to sell more of his books?
And the last question:
Does anyone reads his replies in full or just stops after words "You should know..." and then, as usual, "ISO..."
Anyway, for all his fans: http://joecelkothesqlapprentice.blogspot.com/:
:hehe:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply