Column total using group by rollup
-
I'm trying to total all the rows but one in a column using group by rollup in SQL Server. I'm getting the below which is correct for all the columns but I'd like to removed the RTF Total-224549.33. Is there a way to have a case statement in the group by rollup or what other way can I have that row not counted so the total is 2,434,248.74?. Thanks.
- This topic was modified 4 years, 7 months ago by smattiko83.
- This topic was modified 4 years, 7 months ago by smattiko83.
-
could you not put a WHERE clause where your category description is not RTF TOTAL?
Don't think you can do that with a group by; I think it will need to be in a WHERE clause.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I was thinking a WHERE clause as well.
-
That removes the row though. I want to keep the row but not have it included in the total.
-
Could you post some sample code?
My expectation is that you are going to need a case statement in there, but would like some sample data to work with and would like to see what you have tried.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I thought i had it attached in the original post?
-
My bad... didn't see the attachment. Sorry about that.
Looking through it now and will let you know if I see something to do what you need.
EDIT - your attached script is the query you are running, but we have no sample data to go off of. Can you include some sample data so we can actually run your query?
EDIT 2 - possibly this query (did not test it as I have no data) - just change your LAST select to:
SELECT PARENT
,CASE WHEN CATEGORY_DESCRIPTION = '071 - RTF TOTAL' THEN RIGHT(CATEGORY_DESCRIPTION,9) ELSE CATEGORY_DESCRIPTION END AS CATEGORY_DESCRIPTION
,SUM(JANUARY) AS JANUARY
,SUM(FEBRUARY) AS FEBRUARY
,SUM(MARCH) AS MARCH
,SUM(APRIL) AS APRIL
,SUM(MAY) AS MAY
,SUM(JUNE) AS JUNE
,SUM(JULY) AS JULY
,SUM(AUGUST) AS AUGUST
,SUM(SEPTEMBER) AS SEPTEMBER
,SUM(OCTOBER) AS OCTOBER
,SUM(NOVEMBER) AS NOVEMBER
,SUM(DECEMBER) AS DECEMBER
,SUM(AVERAGE) AS AVERAGE
,SUM(STD_DEV) AS STD_DEV
,SUM(STDEV_1_POS) AS STDEV_1_POS
,SUM(STDEV_2_POS) AS STDEV_2_POS
,SUM(STDEV_3_POS) AS STDEV_3_POS
,SUM(STDEV_1_NEG) AS STDEV_1_NEG
,SUM(STDEV_2_NEG) AS STDEV_2_NEG
,SUM(STDEV_3_NEG) AS STDEV_3_NEG
FROM COMBO
WHERE CATEGORY_DESCRIPTION <> '071 - RTF TOTAL'
GROUP BY ROLLUP (PARENT,CATEGORY_DESCRIPTION)
UNION
SELECT PARENT
,RIGHT(CATEGORY_DESCRIPTION,9) AS CATEGORY_DESCRIPTION
,SUM(JANUARY) AS JANUARY
,SUM(FEBRUARY) AS FEBRUARY
,SUM(MARCH) AS MARCH
,SUM(APRIL) AS APRIL
,SUM(MAY) AS MAY
,SUM(JUNE) AS JUNE
,SUM(JULY) AS JULY
,SUM(AUGUST) AS AUGUST
,SUM(SEPTEMBER) AS SEPTEMBER
,SUM(OCTOBER) AS OCTOBER
,SUM(NOVEMBER) AS NOVEMBER
,SUM(DECEMBER) AS DECEMBER
,SUM(AVERAGE) AS AVERAGE
,SUM(STD_DEV) AS STD_DEV
,SUM(STDEV_1_POS) AS STDEV_1_POS
,SUM(STDEV_2_POS) AS STDEV_2_POS
,SUM(STDEV_3_POS) AS STDEV_3_POS
,SUM(STDEV_1_NEG) AS STDEV_1_NEG
,SUM(STDEV_2_NEG) AS STDEV_2_NEG
,SUM(STDEV_3_NEG) AS STDEV_3_NEG
FROM COMBO
WHERE CATEGORY_DESCRIPTION = '071 - RTF TOTAL'
GROUP BY ROLLUP (PARENT,CATEGORY_DESCRIPTION)Basically, the first query (which has the TOTAL in it) is summed but excludes 071 - RTF TOTAL. Then we UNION that where we are ONLY looking at 071 - RTF TOTAL. Probably get a slight performance boost by taking out that RIGHT function as we know we are only getting 071 - RTF TOTAL...
Likely not the most efficient code, but with all of those CTE's already, I am expecting we are going for accuracy, readability and testable code over efficient code.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
It is close. I had to change the second query below or it had another Null like in the screen shot. The only issue now is the order. The Total is at the top and "RTF Total" is at the bottom.
GROUP BY PARENT,RIGHT(CATEGORY_DESCRIPTION,9)
Current sort:
-
Well, the order is likely going to be unreliable anyway as you have no order by clause in there. If you need it in a specific order, you will need to put in an order by clause.
As for the second null column, I see no reason why that is popping up. Which query from the 2 in the UNION is causing that? Comment one of them out and compare the two. The first one should return all rows except the one with RTF - TOTAL and the second should ONLY return RTF TOTAL. My guess is it is the first one, I am just not entirely sure why or how to filter it better.
This would be a lot easier if we had some sample data to work off of though...
Also, the query I provided had a mistake in it - the first part of the UNION should not have had that CASE statement... that was a copy-paste error on my part.
EDIT - I see your post changed on me so mine makes a bit less sense... but anyways, ordering is done by the ORDER BY statement. Without an ORDER BY, the order could come out in any order SQL feels like. If you need it in a specific order, you are going to need to put in an ORDER BY statement.
In this case, I think you are ordering by the category description, so may as well continue ordering by that. Offhand I don't remember if NULLS are ordered first or last, so you may need to do something with that.
That being said, are you certain that the application that is using this data isn't going to reorder it in on its own? Generally, I don't bother ordering the results in SQL unless it is just for me to observe a specific behavior because once I pass it over to the application layer (SSRS, windows application, Power BI, etc), those tools can reorder it on me and the time I spent ordering it the way I wanted in SQL was wasted. And sometimes ordering it on the presentation layer is easier than in the database side.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
The second null went away with the change in the group by. Is there a way to sort and still have the total by parent/category description at the bottom every time the parent changes? I'd like it to look like the first image in my first post. I've attached sample data.
-
Ok, so to sort it how you want, I think this should work:
SELECT CATEGORY_DESCRIPTION
,JANUARY
,FEBRUARY
,MARCH
,APRIL
,MAY
,JUNE
,JULY
,AUGUST
,SEPTEMBER
,OCTOBER
,NOVEMBER
,DECEMBER
,AVERAGE
,STD_DEV
,STDDEV_1_POS
,STDDEV_2,POS
,STDDEV_3_POS
,STDDEV_1_NEG
,STDDEV_2_NEG
,STDDEV_3_NEG
FROM (
SELECT PARENT
,CATEGORY_DESCRIPTION AS CATEGORY_DESCRIPTION
,SUM(JANUARY) AS JANUARY
,SUM(FEBRUARY) AS FEBRUARY
,SUM(MARCH) AS MARCH
,SUM(APRIL) AS APRIL
,SUM(MAY) AS MAY
,SUM(JUNE) AS JUNE
,SUM(JULY) AS JULY
,SUM(AUGUST) AS AUGUST
,SUM(SEPTEMBER) AS SEPTEMBER
,SUM(OCTOBER) AS OCTOBER
,SUM(NOVEMBER) AS NOVEMBER
,SUM(DECEMBER) AS DECEMBER
,SUM(AVERAGE) AS AVERAGE
,SUM(STD_DEV) AS STD_DEV
,SUM(STDEV_1_POS) AS STDEV_1_POS
,SUM(STDEV_2_POS) AS STDEV_2_POS
,SUM(STDEV_3_POS) AS STDEV_3_POS
,SUM(STDEV_1_NEG) AS STDEV_1_NEG
,SUM(STDEV_2_NEG) AS STDEV_2_NEG
,SUM(STDEV_3_NEG) AS STDEV_3_NEG
,CASE WHEN CATEGORY_DESCRIPTION IS NULL THEN 1000 ELSE CAST(LEFT(CATEGORY_DESCRIPTION,2) AS INT) END AS Order
FROM COMBO
WHERE CATEGORY_DESCRIPTION <> '071 - RTF TOTAL'
GROUP BY ROLLUP (PARENT,CATEGORY_DESCRIPTION)
UNION
SELECT PARENT
,RIGHT(CATEGORY_DESCRIPTION,9) AS CATEGORY_DESCRIPTION
,SUM(JANUARY) AS JANUARY
,SUM(FEBRUARY) AS FEBRUARY
,SUM(MARCH) AS MARCH
,SUM(APRIL) AS APRIL
,SUM(MAY) AS MAY
,SUM(JUNE) AS JUNE
,SUM(JULY) AS JULY
,SUM(AUGUST) AS AUGUST
,SUM(SEPTEMBER) AS SEPTEMBER
,SUM(OCTOBER) AS OCTOBER
,SUM(NOVEMBER) AS NOVEMBER
,SUM(DECEMBER) AS DECEMBER
,SUM(AVERAGE) AS AVERAGE
,SUM(STD_DEV) AS STD_DEV
,SUM(STDEV_1_POS) AS STDEV_1_POS
,SUM(STDEV_2_POS) AS STDEV_2_POS
,SUM(STDEV_3_POS) AS STDEV_3_POS
,SUM(STDEV_1_NEG) AS STDEV_1_NEG
,SUM(STDEV_2_NEG) AS STDEV_2_NEG
,SUM(STDEV_3_NEG) AS STDEV_3_NEG
,CAST(LEFT(CATEGORY_DESCRIPTION,2) AS INT) AS Order
FROM COMBO
WHERE CATEGORY_DESCRIPTION = '071 - RTF TOTAL'
GROUP BY PARENT,RIGHT(CATEGORY_DESCRIPTION,9)
) AS Data
ORDER BY Order ASCHere we are putting in an "order" value and that is based on either the first 2 digits of your category description (which should work based on the use cases you have in your sample code) OR an arbitrarily larger number. We don't select the Order column, so it only exists in the subquery and isn't presented to the end user. In the event you have larger than 99 for a starting value OR you have some that don't start with numbers, you will need to do some extra checks on category description.
But, again, I would caution you on using something like this... if the application layer has some built-in ordering mechanism, this may just be overhead with no benefit.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
That is it! Thank you!
I wish I did not have to sort this way or sum the two rows but was told they have been looking at it this way since 2007 and they cannot change.
Thanks again.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply