GROUPING

  • Comments posted to this topic are about the item GROUPING

  • Thanks Steve, a good to start the week!

    ...

  • Use GROUPING SETS fairly regularly. Have never used the GROUPING() function before.
    Learned something new, thanks Steve

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

  • Can I be the first to confess - I don't get this! (And from the number of incorrect answers, it's not just me)
    I have read the Grouping Sets and Grouping() function docs and the light has not yet dawned. Clearly need to go though it again (and probably again ...). I do not use Grouping Sets at the moment, so I am clearly missing out on something useful
    Thanks, Steve

  • This documentation from Technet GROUPING SETS Equivalents helped me,
    and then experimenting with an example in DOCS GROUPING (Transact-SQL)

    SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
    FROM Sales.SalesPerson
    -- GROUP BY GROUPING SETS (SalesQuota) WITH ROLLUP;
    -- GROUP BY GROUPING SETS (SalesQuota);
    GROUP BY GROUPING SETS (SalesQuota,());

  • GDownie - Monday, January 15, 2018 2:54 AM

    Can I be the first to confess - I don't get this! (And from the number of incorrect answers, it's not just me)
    I have read the Grouping Sets and Grouping() function docs and the light has not yet dawned. Clearly need to go though it again (and probably again ...). I do not use Grouping Sets at the moment, so I am clearly missing out on something useful
    Thanks, Steve

    I don't get it either.  The attached page says "Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set."
    However, later on the page it does talk about null values but doesn't really explain how they affect the income.

    Should the second sentence really be "GROUPING returns 1 for aggregated when the value is null or 0 for not aggregated or the value is not null in the result set."

  • Yep, bit of a head scratcher.  Confess got it right by a guess. 😉

  • Marcia J - Monday, January 15, 2018 5:16 PM

    GDownie - Monday, January 15, 2018 2:54 AM

    Can I be the first to confess - I don't get this! (And from the number of incorrect answers, it's not just me)
    I have read the Grouping Sets and Grouping() function docs and the light has not yet dawned. Clearly need to go though it again (and probably again ...). I do not use Grouping Sets at the moment, so I am clearly missing out on something useful
    Thanks, Steve

    I don't get it either.  The attached page says "Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set."
    However, later on the page it does talk about null values but doesn't really explain how they affect the income.

    Should the second sentence really be "GROUPING returns 1 for aggregated when the value is null or 0 for not aggregated or the value is not null in the result set."

    GROUPING is used for expressions that appear in one or more grouping sets to specify whether they are included in the current grouping set.  A NULL value can either be because the underlying data is NULL or because an expression is the result of an aggregate (because it's not included in the current grouping set).  For example, you can see in the MONTH that NULL values appear for both the values of GROUPING(MONTH(Graduation_Date)).

    DECLARE @Students TABLE
    (
        Student_ID INT NOT NULL,
        Graduation_Date DATE NULL
    )

    INSERT @Students(Student_ID, Graduation_Date)
    VALUES
        (1, '2016-06-15'),
        (2, '2016-08-30'),
        (3, '2016-12-15'),
        (4, '2017-06-15'),
        (5, '2017-08-30'),
        (6, '2018-06-14'),
        (7, NULL),
        (8, NULL),
        (9, NULL),
        (10, NULL)

    SELECT
        GROUPING(YEAR(Graduation_Date)) AS Grad_Year_grp,
        Year(Graduation_Date) AS Grad_Year,
        GROUPING(MONTH(Graduation_Date)) AS Grad_Month_grp,
        MONTH(Graduation_Date) AS Grad_Month,
        COUNT(*) AS Cnt
    FROM @Students
    GROUP BY GROUPING SETS( (YEAR(Graduation_Date), MONTH(Graduation_Date)), (YEAR(Graduation_Date)))

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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