Getting Subtotals

Question

Post reply

Getting Subtotals

Steve Jones - SSC Editor

SSC Guru

Points: 736291
More actions
July 6, 2020 at 12:00 am

#3763282

Comments posted to this topic are about the item Getting Subtotals

Viewing 8 posts - 1 through 7 (of 7 total)

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 1

Hi Steve. I think this is a good and interesting question, though I noticed a few issues:

The GROUP BY expr WITH xxxx syntax is deprecated and shouldn't be used or promoted. For the given options, the query should end with:
GROUP BY xxxx(ps.ProductCat, MONTH(saledate))
The question states: "I want to run a sum by month." Yet the preferred answer returns subtotals for ProductCat, not for MONTH(saledate).
The question also states: "Which keyword(s) ... gives me less rows in the result set?". While the preferred answer does satisfy this condition, the implication is that CUBE is otherwise equivalent. But that implication is incorrect as the CUBE option actually does provide subtotals for MONTH(saledate).

Based on wanting a) subtotals by month, and b) fewer rows, the actual correct answer is to use CUBE and a HAVING clause (to filter out the unwanted rows), and no WITH clause:

SELECT 
ps.ProductCat
, MONTH(saledate) AS [Month]
, SUM(ps.SaleTotal) AS TotalSales
FROM dbo.ProductSales AS ps
GROUP BY CUBE(ps.ProductCat, MONTH(saledate))
HAVING MONTH(saledate) IS NOT NULL;

That returns:

ProductCat    Month    TotalSales
Laptop        1        300.00
NULL          1        300.00
Laptop        2        300.00
Mobile        2         50.00
Watch         2        600.00
NULL          2        950.00
Mobile        3        100.00
NULL          3        100.00

Take care,

Solomon....

This reply was modified 4 years, 5 months ago by Solomon Rutzky.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Stewart "Arturius" Campbell SSC Guru Points: 72629 More actions · Answer 2

Nice question, Steve

and thanks for the detailed explanation, Solomon.

Actually, the fact that WITH CUBE had been deprecated threw me...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 3

Thanks for the notes. I'll update the question, but I don't see GROUP BY xxx WITH CUBE|ROLLUP deprecated. It is noted as backwards compatibility and not ISO standard, but not deprecated. GROUP BY ALL is specifically deprecated, but if you have a reference, I'd be interested to know.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

Steve Jones - SSC Editor wrote:

Thanks for the notes. I'll update the question, but I don't see GROUP BY xxx WITH CUBE|ROLLUP deprecated. It is noted as backwards compatibility and not ISO standard, but not deprecated.

Hi Steve. You're welcome :-). And regarding it being deprecated, perhaps I misspoke. I was inferring that from the note about it being for "backwards compatibility only", hence do not use in new development. I suppose that's not technically the same thing as "marked for removal", but I'm also not sure that it's really any different. Hmm. Still, there is no official deprecation notice on the page, though this wouldn't be the first time that such a notice has been missing: I recently came across 2 system stored procedures that either weren't fully listed as deprecated, or not consistently labeled as such across the various places where either the procs or deprecated items are listed (sp_change_users_login and sp_changedbowner). Both have been fixed / clarified. I suppose I can open an issue on the WITH ROLLUP | CUBE syntax to get clarification.

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 5

Steve Jones - SSC Editor wrote:

It is noted as backwards compatibility and not ISO standard, but not deprecated. GROUP BY ALL is specifically deprecated, but if you have a reference, I'd be interested to know.

Actually, I was typing up the issue and re-read the notice. Like the sp_change_users_login documentation issue I noted earlier, it's not consistently stated. At the top of the page, in the syntax box, it shows:

-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database
GROUP BY 
      [ ALL ] column-expression [ ,...n ] 
    | column-expression [ ,...n ] [ WITH { CUBE | ROLLUP } ]

And in the arguments section it states:

GROUP BY [ ALL ] column-expression [ ,...n ]
Applies to: SQL Server and Azure SQL Database
NOTE: This syntax is provided for backward compatibility only. It will be removed in a future version. Avoid using this syntax in new development work, and plan to modify applications that currently use this syntax.

The "note" does seem to equate "backward compatibility only" with "will be removed in a future version" (though that is not proof). I find it ambiguous as the WITH { CUBE | ROLLUP } option is not in the "Arguments" section of the documentation. They seem to have forgotten about it. Also, the syntax for both of these options is incorrect as it shows them both to be optional, but omitting them would leave it as the basic GROUP BY syntax which is clearly still valid. The syntax should be:

GROUP BY {
          ALL column-expression [ ,...n ] 
        | column-expression [ ,...n ] WITH { CUBE | ROLLUP }
         }

I also realized that I could test for this directly since deprecated features raise deprecation events. I ran the ALL, WITH CUBE, and WITH ROLLUP variations and checked using:

SELECT *
FROM   sys.dm_os_performance_counters
WHERE  [object_name] = N'SQLServer:Deprecated Features'
AND    [cntr_value] > 0;

And only GROUP BY ALL showed up. So, I will submit the issue asking them to make the doc clearer about the syntax and which options are, and are not, deprecated.

Take care, Solomon..

This reply was modified 4 years, 5 months ago by Solomon Rutzky.
This reply was modified 4 years, 5 months ago by Solomon Rutzky.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 6

Thanks for the research. That matches what I see.

GROUP BY ALL is specifically on the deprecation list for 2016/2019, and noted on the GROUP BY page. The WITH xxx syntax isn't, and the "backwards compatibility" phrase isn't something I've seen.

There have been minor mentions that nothing is going to be removed. It just won't get work to keep up with other changes. However, the GROUP BY CUBE, which is the ISO part, is cleaner IMHO. I've reworded the question for that and I'll try to practice using that in my own work.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 7

Steve Jones - SSC Editor wrote:

Thanks for the research. That matches what I see.
There have been minor mentions that nothing is going to be removed.

You're welcome. And yes, that's what I've been told as well (unless they find a security hole in an old feature, then it might be removed).

I finally had time to submit the request for the GROUP BY changes:

Syntax and deprecation clarification improvements for "SELECT - GROUP BY"

Take care, Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR