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.
- Postgres selecting from table row
- To find the next work day
- SQL Server Left Join With 'Or' Operator
- How to exclusivley select a record from different tables
- Add user to table and use users id while adding data to second table while user registration
- Selecting the earliest row where a specified date value falls between a date range (two columns) when multiple rows may match
- SQL AVG returning an int
- Using two tables. What am I doing wrong here?
- ORDER BY column, with specific notnull value LAST
- How to get the first available number in a range
- In GENERATE_SPREADSHEET SQL on IBM i there are problems if a $ character is present in qualified names
- How to return default value from SQL query
- how to sort a column which contains Semantic Version in Postgres
- Proper Case in Athena SQL
- SQL VBA Query not picking up letter characters, only fully numeric fields
- Joining two tables with a joint result in Postgresql
- Can I Query or Do I Need a Table Function?
- How to link 3 SQL tables and pass 0 value if the value is NULL
- Searching all tables in database containing specific column
- Convert VARCHAR to SMALLDATETIME as part of UPDATE statement
- WordPress - Optimize the Meta Query for 3 meta keys at a time
- How can I find the highest ancestor in a tree?
- Firebird lock and select unlocked fields
- How to pass parameters in a native query JPA
- SQL Server : query columns to JSON object with group by
- SQL Server While loop for last day of month
- Can anyone please explain this solution of hacker rank Binary Tree Nodes?
- generate DDL script from SQL Server database
- How to return name, given an ID Number from same dataset?
- Get the names of all the tables and the views in a single SQL query for SQL Server?