January 13, 2018 at 12:31 pm
Thanks Steve, a good to start the week!
...
January 15, 2018 at 12:34 am
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”
January 15, 2018 at 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
January 15, 2018 at 2:43 pm
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,());
January 15, 2018 at 5:16 pm
GDownie - Monday, January 15, 2018 2:54 AMCan 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."
January 16, 2018 at 2:12 am
Yep, bit of a head scratcher. Confess got it right by a guess. 😉
January 16, 2018 at 12:37 pm
Marcia J - Monday, January 15, 2018 5:16 PMGDownie - Monday, January 15, 2018 2:54 AMCan 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, SteveI 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