Getting Subtotals

  • Comments posted to this topic are about the item Getting Subtotals

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

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

    2. The question states: "I want to run a sum by month." Yet the preferred answer returns subtotals for ProductCat, not for MONTH(saledate).
    3. 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....

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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”

  • 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.

  • 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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..

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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.

     

  • 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

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

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