sqlsql-serversql-server-2008t-sqlgrouping-sets
Grouping sets is not aggregating results
I've only recently started using this function, so forgive this question if it is imbecilic.
I have an existing query, which I've realised could be helped with the use of this query to create additional group by groupings, akin to subtotals.
In this example, I am wanting to aggregate in a hierarchy like this:
Year > Month > Profit Centre > Date
An example of the results this query is bringing back
As you can see my grouping sets are returning zero. My question is, why?
My query is below
SELECT
datepart(yyyy,overview.Date) as 'Year',
datepart(mm,overview.Date) as'Month',
overview.Date as 'Date',
overview.[Profit Centre],
overview.[Current
Year Group Bookings],
overview.[Previous Year Group Bookings],
overview.[Current Year Total Covers],
overview.[Previous Year Total Covers],
overview.[Large Group Count]
FROM
(
SELECT
DISTINCT
CONVERT(date,csd.tendered_date_time) as 'Date',
PCM.profit_center_name as 'Profit Centre',
PCD.profit_center_id,
(
SELECT count(csd2.num_covers)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) =
CONVERT(date,CSD.tendered_date_time)
AND CSD2.num_covers >=
(CASE
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8'
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Current Year Group Bookings',
(
SELECT count(csd2.num_covers)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) =
CONVERT(date,DATEADD(month,-12,CSD.tendered_date_time))
AND CSD2.num_covers >=
(CASE
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8'
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Previous Year Group Bookings',
(
SELECT ISNULL(SUM(csd2.num_covers),0)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) =
CONVERT(date,CSD.tendered_date_time)
AND CSD2.num_covers >=
(CASE
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8'
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Current Year Total Covers',
(
SELECT ISNULL(SUM(csd2.num_covers),0)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) =
CONVERT(date,DATEADD(month,-12,CSD.tendered_date_time))
AND CSD2.num_covers >=
(CASE
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8'
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Previous Year Total Covers',
(CASE
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8'
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END) AS 'Large Group Count'
FROM ig_business..Check_Sales_Detail CSD (NOLOCK)
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD (NOLOCK) ON
PCD.profit_center_dim_id = CSD.profit_center_dim_id and PCD.ent_id = 1
INNER JOIN it_cfg..Profit_Center_Master PCM (NOLOCK) ON PCM.profit_center_id
= PCD.profit_center_id and PCm.ent_id = 1
WHERE
(
(pcd.profit_center_id = '77' AND csd.num_covers > 8)
OR
(pcd.profit_center_id = '13' AND csd.num_covers >= 10)
OR
(pcd.profit_center_id IN ('60','61','62','63','64','65') AND
csd.num_covers > 16)
OR
(pcd.profit_center_id = 14 AND csd.num_covers >= 10)
OR
(pcd.profit_center_id = 90 AND csd.num_covers >= 10)
OR
(pcd.profit_center_id = 98 AND csd.num_covers >= 8)
OR
(pcd.profit_center_id IN ('74','101') AND csd.num_covers >= 10)
OR
(pcd.profit_center_id = '194' AND csd.num_covers >= 10)
OR
(pcd.profit_center_id = '49' AND csd.num_covers >= 15)
OR
(pcd.profit_center_id IN ('20','21','22','200') AND csd.num_covers >= 8)
OR
(pcd.profit_center_id = '74' AND csd.num_covers >= 15)
OR
(pcd.profit_center_id = '26' AND csd.num_covers >= 10)
OR
(pcd.profit_center_id IN ('86','68') AND csd.num_covers >= 8)
OR
(pcd.profit_center_id IN ('40','41','42','43') AND csd.num_covers >= 8)
OR
(pcd.profit_center_id IN ('96','98','99') AND csd.num_covers >= 10)
OR
(pcd.profit_center_id = '57' AND csd.num_covers >= 10)
)
AND
CSD.tendered_date_time > CONVERT(date,'2017-01-01') -- We only want
comparisons from today going forward, i.e. not historical
GROUP BY
GROUPING SETS
(
(csd.tendered_date_time, PCM.profit_center_name,PCD.profit_center_id),
(datepart(yyyy,csd.tendered_date_time),datepart(mm,csd.tendered_date_time),
pcm.profit_center_name),
()
)
) overview
ORDER BY overview.[Profit Centre] asc, CONVERT(date,overview.Date) asc
Solution
My question is, why (are the grouping sets zero)?
Because you are not using aggregate functions such as SUM() or COUNT() in a manner than can be summarised into sub-totals. You are using a set of "correlated subqueries" which do use COUNT() but by the time these are evaluated the outer query just sees those columns as data (not as aggregates).
example (untested of course):
WITH pcd AS (
SELECT *
FROM ig_Dimension..Profit_Center_Dimension pcd
WHERE (
OR (pcd.profit_center_id = '13' AND csd.num_covers >= 10)
OR (pcd.profit_center_id = 14 AND csd.num_covers >= 10) -- is it an integer? or not?
OR (pcd.profit_center_id = '26' AND csd.num_covers >= 10)
OR (pcd.profit_center_id = '49' AND csd.num_covers >= 15)
OR (pcd.profit_center_id = '57' AND csd.num_covers >= 10)
OR (pcd.profit_center_id = '74' AND csd.num_covers >= 15)
OR (pcd.profit_center_id = '77' AND csd.num_covers > 8)
OR (pcd.profit_center_id = 90 AND csd.num_covers >= 10) -- is it an integer? or not?
OR (pcd.profit_center_id = 98 AND csd.num_covers >= 8) -- is it an integer? or not?
OR (pcd.profit_center_id = '194' AND csd.num_covers >= 10)
OR (pcd.profit_center_id IN ('20', '21', '22', '200') AND csd.num_covers >= 8)
OR (pcd.profit_center_id IN ('40', '41', '42', '43') AND csd.num_covers >= 8)
OR (pcd.profit_center_id IN ('60', '61', '62', '63', '64', '65') AND csd.num_covers > 16)
OR (pcd.profit_center_id IN ('74', '101') AND csd.num_covers >= 10)
OR (pcd.profit_center_id IN ('86', '68') AND csd.num_covers >= 8)
OR (pcd.profit_center_id IN ('96', '98', '99') AND csd.num_covers >= 10)
)
AND pcd.ent_id = 1
)
SELECT
DATEPART(yyyy, csd.tendered_date_time) AS 'Year'
, DATEPART(mm, csd.tendered_date_time) AS 'Month'
, pcd.[Profit Centre]
, pcm.profit_center_name AS 'Profit Centre'
, pcd.profit_center_id
, COUNT(csd.num_covers)
FROM ig_business..Check_Sales_Detail csd
INNER JOIN pcd ON pcd.profit_center_dim_id = csd.profit_center_dim_id
INNER JOIN it_cfg..Profit_Center_Master pcm ON pcm.profit_center_id = pcd.profit_center_id
AND pcm.ent_id = 1
GROUP BY GROUPING SETS
( DATEPART(yyyy, csd.tendered_date_time) AS 'Year'
, DATEPART(mm, csd.tendered_date_time) AS 'Month'
, pcd.[Profit Centre]
, pcm.profit_center_name AS 'Profit Centre'
, pcd.profit_center_id
)
NOTE: you need to be careful about "implicit conversions", if pcd.profit_center_id is an integer then do NOT use '194', but if it is a varchar or similar then do not use integers. I also suggest you try to keep that where clause in some "order" so it is easier to read.
- How to create table like another table with additional columns in MYSQL
- Connecting to Microsoft SQL server using Python
- How to use STRING_AGG function inside another STRING_AGG function in SQL Server
- Clean up a SQL Server Query with too many DISTINCT clauses in it
- How to decrypt stored procedure in SQL Server 2008
- In DuckDB, Is it possible to change a value based on the previous value ordered by another column without using UDFs?
- Perform an INNER JOIN with more than 2 tables in MySQL
- How To Get Entire Linked Group Details using SQL?
- Sort Month Name in SQL Query
- What does the new stored procedure default content mean?
- SQL check if timestamp is greater than 24 hours from now (MYSQL)
- How to prevent Spring Boot/Hibernate from converting entity column names from PascalCase to snake_case?
- How to stop IntelliJ from reformatting SQL code?
- SQL - Query to find if a string contains part of the value in Column
- How to distinguish when FOUND is false from when row version is obsolete in a Postgres stored function
- select multiple cells inner join one table
- SSIS Error: External Column for Source out of sync with Data Source columns; How do I remove External Columns?
- sum case with left join
- MySQL Query performance - best partial match
- Why cant I use GROUP BY in this Query
- How to use Table -Valued Parameter with Dynamic query
- Count 'IN' value in MySQL from multiple non-normalized columns
- Postgres - How to use the AVG of the last number of rows and multiply it with another column?
- SQL select join: is it possible to prefix all columns as 'prefix.*'?
- Convert SQL containing a subquery and multiple OR WHERE conditions to CodeIgniter's active record syntax
- Order By is not working using "WITH" Clause
- How to return NULL when result is empty?
- How to find the value with date closest to another date
- Truncate (not round) decimal places in SQL Server
- Extract dirname from full path stored in database