SUBTOTALS in Pivot

Hi there

I am trying to produce a table that looks like this:

As you can see it has subtotals and a grand total.

My query is working (the data in the table is slightly wrong, due to how it was downloaded from the database), but I don't know how to put in the subtotals and the grand total. I have made an attempt below.

--Incident Reporting for Restraint Committee
use [exoMashTrust-test]
declare
@startdate datetime
,@enddate datetime
--Change these for each month's report
SET @startdate = '20150501 00:00:00.000'
SET @enddate = '20150531 23:59:59.999'
--Pivot Listing
SELECT *
FROM (
	SELECT
		PIP.INCIDENT_NAME as [Category]
		,PIO.OPTION_NAME as [Incident Type]
		,CASE
			WHEN
				PATI.INCIDENT_AREA IN (7,8,76,9,10,11,12,13,70,72,75,73,74,71,27,69,28,29,30,31,32,67,33,34,80,35,36,37,38,39)
				THEN 'ID'
			WHEN 
				PATI.INCIDENT_AREA = 22
				THEN 'PD'
			WHEN 
				PATI.INCIDENT_AREA IN (48,49,41,42)
				THEN 'MH Res PN'
			WHEN 
				PATI.INCIDENT_AREA IN (47,52,46,45,43,44,81)
				THEN 'MH Res Wgtn'
			WHEN 
				PATI.INCIDENT_AREA IN (50,51)
				THEN 'Terrace End'
			WHEN 
				PATI.INCIDENT_AREA IN (18,53,77,78)
				THEN 'AOD/DD'
			WHEN 
				PATI.INCIDENT_AREA IN (25,24,23,79)
				THEN 'Vocational'
			WHEN 
				PATI.INCIDENT_AREA IN (25,24,23,79)
				THEN 'Vocational'
			WHEN 
				PATI.INCIDENT_AREA = 6
				THEN 'Te Matai'
			WHEN 
				PATI.INCIDENT_AREA = 40
				THEN 'Luck'
			WHEN 
				PATI.INCIDENT_AREA in (19,20)
				THEN 'Offices'
			WHEN 
				PATI.INCIDENT_AREA = 6
				THEN 'Healthy Lifestyles'
			WHEN 
				PATI.INCIDENT_AREA in (55,56,57,60,63,64,16,17,58,59)
				THEN 'MH Level 2 PN'
			WHEN 
				PATI.INCIDENT_AREA = 66
				THEN 'MH Bluegum'
			ELSE 'Other'
			END AS [Area]
		,pati.INCIDENT_DATE as [Date of Incident]
	FROM 
		PATIENT_INCIDENTS AS pati
		JOIN PATIENT_INC_PARENT AS PIP
		ON PATI.PARENT_ID=PIP.SEQNO
		JOIN PATIENT_INC_OPTIONS AS pio 
		ON pio.SEQNO = pati.OPTION_ID
		JOIN PATIENT_INC_AREA AS PIA
		ON PIA.AREA_ID=PATI.INCIDENT_AREA
	WHERE 
		pati.PARENT_ID IN (3,6,9)
		AND PATI.INCIDENT_DATE BETWEEN @startdate AND @enddate
	----SUBTOTAL ATTEMPT
	--GROUP BY
	--	PIP.INCIDENT_NAME 
	--	,PIO.OPTION_NAME
	--	,CASE
	--		WHEN
	--			PATI.INCIDENT_AREA IN (7,8,76,9,10,11,12,13,70,72,75,73,74,71,27,69,28,29,30,31,32,67,33,34,80,35,36,37,38,39)
	--			THEN 'ID'
	--		WHEN 
	--			PATI.INCIDENT_AREA = 22
	--			THEN 'PD'
	--		WHEN 
	--			PATI.INCIDENT_AREA IN (48,49,41,42)
	--			THEN 'MH Res PN'
	--		WHEN 
	--			PATI.INCIDENT_AREA IN (47,52,46,45,43,44,81)
	--			THEN 'MH Res Wgtn'
	--		WHEN 
	--			PATI.INCIDENT_AREA IN (50,51)
	--			THEN 'Terrace End'
	--		WHEN 
	--			PATI.INCIDENT_AREA IN (18,53,77,78)
	--			THEN 'AOD/DD'
	--		WHEN 
	--			PATI.INCIDENT_AREA IN (25,24,23,79)
	--			THEN 'Vocational'
	--		WHEN 
	--			PATI.INCIDENT_AREA IN (25,24,23,79)
	--			THEN 'Vocational'
	--		WHEN 
	--			PATI.INCIDENT_AREA = 6
	--			THEN 'Te Matai'
	--		WHEN 
	--			PATI.INCIDENT_AREA = 40
	--			THEN 'Luck'
	--		WHEN 
	--			PATI.INCIDENT_AREA in (19,20)
	--			THEN 'Offices'
	--		WHEN 
	--			PATI.INCIDENT_AREA = 6
	--			THEN 'Healthy Lifestyles'
	--		WHEN 
	--			PATI.INCIDENT_AREA in (55,56,57,60,63,64,16,17,58,59)
	--			THEN 'MH Level 2 PN'
	--		WHEN 
	--			PATI.INCIDENT_AREA = 66
	--			THEN 'MH Bluegum'
	--		ELSE 'Other'
	--		END
	--	,pati.INCIDENT_DATE 
	--WITH ROLLUP
	) AS A
pivot(count([Date of Incident]) FOR [Area] IN (
		[ID]
		,[PD]
		,[MH Res PN]
		,[MH Res Wgtn]
		,[MH Bluegum]
		,[MH Level 2 PN]
		,[Terrace End]
		,[AOD/DD]
		,[Vocational]
		,[Te Matai]
		,[LUCK]
		,[Offices]
		,[Healthy Lifestyles]
		,[Other]
		)) AS p
ORDER BY
	Category
;

This is what I get with the subtotal line commented out:

When I uncomment those lines I get this, so my code is clearly wrong. But I can't figure out what to do.

Hopefully someone can help.

Normally, these types of rollups are better handled in the application layer. However, to do it in sql, try an approach like this:

  1. Get the PIVOT part correct ( you seem to be on your way to that) so that you get the correct totals per category.
  2. Use the PIVOT part as a subquery (or CTE if you like) to an outer query
  3. In the outer query, add the subtotal and grand totals by by grouping on Category WITH ROLLUP

decent description here:https://www.sqlpassion.at/archive/2014/09/22/grouping-sets-the-cube-and-rollup-subclauses/

Hi, thanks for your help. I have read the article on the link and tried to get it working for another query I have to do first.

Here is my standard pivot:

--Standard Pivot 
--This works
select *
from (
select 
	[Wait Time Days]
	,[Age Group]
	,[Referral ID]
from
(select distinct
		ppr.SEQNO as [Referral ID]
		,pd.NHI_NUMBER as [NHI]
		,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
		,case
			when ppr.ReferralEndDate = '18991230 00:00:00.000'
			then ''
			else convert(Char(10),ppr.ReferralEndDate,103)
			end as [Referral End Date]
		,ppe.LocalShortCode as [Referral End Code]
		,ppf.LocalShortCode as [Referral From Code]
		,pptd.LocalCode as [Team Code]
		,pd.SEX as [Sex]
		,case
			pet.DESCRIPTION 
				when 'Maori' then 'Maori'
				when 'Pacific Island' then 'Pacific Island'
				when null then 'Other'
				when '' then 'Other'
				else 'Other'
			end	as Ethnicity
		,convert(char(10),pd.DOB,103) as [Date of Birth]
		,datediff(yy,pd.dob,@EndDate) as [Age]
		,case
			when datediff(yy,pd.dob,GETDATE()) < 19 then '0-19'
			when datediff(yy,pd.dob,GETDATE()) between 20 and 64 then '20-64'
			else '65+'
			end as [Age Group]
		,RANK() over 
			(partition by ppr.seqno
			order by ppa.seqno
			)as [First Activity]
		--,ppa.SEQNO as [Activity ID]
		,(convert(Char(10),ppa.ActivityStartDate,103)) AS [Activity Start Date]
		,ppas.LocalShortCode as [Activity Setting Code]
		,ppat.LocalShortCode as [Activity Type Code]
		,datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) as [Wait Days]
		,case
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) =0
			then '0 - No Wait'
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) between 1 and 21
			then '1-21 days'
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) between 22 and 56
			then '22-56 days'
			else '>=56 days'
			end as [Wait Time Days]
	from
			patient_primhd_referral as ppr
			FULL outer join PATIENT_PRIMHD_ACTIVITY AS ppa
			on ppr.SEQNO=ppa.REFERRAL_SEQNO
			full outer join  patient_details as pd
			on ppa.PATIENT_ID=pd.PATIENT_ID
			full outer join  PATIENT_ETH_TYPES as pet
			on pet.SEQNO=pd.ETHNICITY
			full outer join PATIENT_PRIMHD_FROMCODE as ppf
			ON ppr.ReferralFromID=ppf.SEQNO
			full outer join PATIENT_PRIMHD_TEAM_DETAILS as pptd
			on ppa.ReferralTeamID=pptd.SEQNO
			full outer join PATIENT_PRIMHD_ACTIVITY_TYPE AS ppat
			on ppa.ActivityTypeCodeID=ppat.SEQNO
			full outer join PATIENT_PRIMHD_ENDCODE AS PPE
			on ppr.ReferralEndCodeID=ppe.SEQNO
			full outer join PATIENT_PRIMHD_ACTIVITY_SETTING as ppas
			on ppa.ActivitySettingID=ppas.SEQNO
where
	--Referral Start date is between PMR period
	ppr.ReferralStartDate >=@StartDate
	and ppr.ReferralStartDate <@EndDate
	--Team Codes 010071, 7697 lookup on pptd.seqno
	and pptd.SEQNO in (25,29)
	--Activity Type Code excludes T08, T33, T35, T37
	and ppat.LocalShortcode not in ('T08','T08 173','T08 174','T08 175','T08 179','T33','T35','T37')
	--Activity Setting Code excludes WR, SM, PH, OM
	and ppas.LocalShortCode not in ('WR', 'SM','PH','OM')
	--Test for Data from DHB
	--and ppr.SEQNO in (6727,7096)
	) as r
where r.[First Activity] = 1) as f
pivot(count([Referral ID]) FOR [Age Group] IN (
			[0-19]
			,[20-64]
			,[65+]
			)) AS p
order by
	[Wait Time Days] asc

This produces these results, which are right:

Then I tried to incorporate the syntax in the article:

--Pivot with SubTotals
select *
from (
select 
	[Wait Time Days]
	,[Age Group]
	,[Referral ID]
	,SUM([Wait Days]) as [Total]
from
(select distinct
		ppr.SEQNO as [Referral ID]
		,pd.NHI_NUMBER as [NHI]
		,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
		,case
			when ppr.ReferralEndDate = '18991230 00:00:00.000'
			then ''
			else convert(Char(10),ppr.ReferralEndDate,103)
			end as [Referral End Date]
		,ppe.LocalShortCode as [Referral End Code]
		,ppf.LocalShortCode as [Referral From Code]
		,pptd.LocalCode as [Team Code]
		,pd.SEX as [Sex]
		,case
			pet.DESCRIPTION 
				when 'Maori' then 'Maori'
				when 'Pacific Island' then 'Pacific Island'
				when null then 'Other'
				when '' then 'Other'
				else 'Other'
			end	as Ethnicity
		,convert(char(10),pd.DOB,103) as [Date of Birth]
		,datediff(yy,pd.dob,@EndDate) as [Age]
		,case
			when datediff(yy,pd.dob,GETDATE()) < 19 then '0-19'
			when datediff(yy,pd.dob,GETDATE()) between 20 and 64 then '20-64'
			else '65+'
			end as [Age Group]
		,RANK() over 
			(partition by ppr.seqno
			order by ppa.seqno
			)as [First Activity]
		--,ppa.SEQNO as [Activity ID]
		,(convert(Char(10),ppa.ActivityStartDate,103)) AS [Activity Start Date]
		,ppas.LocalShortCode as [Activity Setting Code]
		,ppat.LocalShortCode as [Activity Type Code]
		,datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) as [Wait Days]
		,case
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) =0
			then '0 - No Wait'
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) between 1 and 21
			then '1-21 days'
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) between 22 and 56
			then '22-56 days'
			else '>=56 days'
			end as [Wait Time Days]
	from
			patient_primhd_referral as ppr
			FULL outer join PATIENT_PRIMHD_ACTIVITY AS ppa
			on ppr.SEQNO=ppa.REFERRAL_SEQNO
			full outer join  patient_details as pd
			on ppa.PATIENT_ID=pd.PATIENT_ID
			full outer join  PATIENT_ETH_TYPES as pet
			on pet.SEQNO=pd.ETHNICITY
			full outer join PATIENT_PRIMHD_FROMCODE as ppf
			ON ppr.ReferralFromID=ppf.SEQNO
			full outer join PATIENT_PRIMHD_TEAM_DETAILS as pptd
			on ppa.ReferralTeamID=pptd.SEQNO
			full outer join PATIENT_PRIMHD_ACTIVITY_TYPE AS ppat
			on ppa.ActivityTypeCodeID=ppat.SEQNO
			full outer join PATIENT_PRIMHD_ENDCODE AS PPE
			on ppr.ReferralEndCodeID=ppe.SEQNO
			full outer join PATIENT_PRIMHD_ACTIVITY_SETTING as ppas
			on ppa.ActivitySettingID=ppas.SEQNO
where
	--Referral Start date is between PMR period
	ppr.ReferralStartDate >=@StartDate
	and ppr.ReferralStartDate <@EndDate
	--Team Codes 010071, 7697 lookup on pptd.seqno
	and pptd.SEQNO in (25,29)
	--Activity Type Code excludes T08, T33, T35, T37
	and ppat.LocalShortcode not in ('T08','T08 173','T08 174','T08 175','T08 179','T33','T35','T37')
	--Activity Setting Code excludes WR, SM, PH, OM
	and ppas.LocalShortCode not in ('WR', 'SM','PH','OM')
	--Test for Data from DHB
	--and ppr.SEQNO in (6727,7096)
	) as r
where r.[First Activity] = 1) as f
pivot(count([Referral ID]) FOR [Age Group] IN (
			[0-19]
			,[20-64]
			,[65+]
			)) AS p
group by rollup(
	[Wait Time Days]
	,[Age Group]
	,[Referral ID]
	,[Wait Days])
;

But I think I have missed something, as I get these errors:

This relates to the first SELECT statement:

Msg 8120, Level 16, State 1, Line 13
Column 'r.Wait Time Days' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

These relate to the last three lines of the GROUP BY ROLLUP

Msg 207, Level 16, State 1, Line 103
Invalid column name 'Age Group'.
Msg 207, Level 16, State 1, Line 104
Invalid column name 'Referral ID'.
Msg 207, Level 16, State 1, Line 105
Invalid column name 'Wait Days'.

I am not sure what I have done wrong.

Group by is missing the select
add the below code
group by [Wait Time Days],[Age Group],[Referral ID]

before ") as f"
after "where r.[First Activity] = 1"

Thanks, I have made the changes and still get the same error.

My query now:

--Pivot with SubTotals
select *
from (
select 
	[Wait Time Days]
	,[Age Group]
	,[Referral ID]
	,SUM([Wait Days]) as [Total]
from
(select distinct
		ppr.SEQNO as [Referral ID]
		,pd.NHI_NUMBER as [NHI]
		,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
		,case
			when ppr.ReferralEndDate = '18991230 00:00:00.000'
			then ''
			else convert(Char(10),ppr.ReferralEndDate,103)
			end as [Referral End Date]
		,ppe.LocalShortCode as [Referral End Code]
		,ppf.LocalShortCode as [Referral From Code]
		,pptd.LocalCode as [Team Code]
		,pd.SEX as [Sex]
		,case
			pet.DESCRIPTION 
				when 'Maori' then 'Maori'
				when 'Pacific Island' then 'Pacific Island'
				when null then 'Other'
				when '' then 'Other'
				else 'Other'
			end	as Ethnicity
		,convert(char(10),pd.DOB,103) as [Date of Birth]
		,datediff(yy,pd.dob,@EndDate) as [Age]
		,case
			when datediff(yy,pd.dob,GETDATE()) < 19 then '0-19'
			when datediff(yy,pd.dob,GETDATE()) between 20 and 64 then '20-64'
			else '65+'
			end as [Age Group]
		,RANK() over 
			(partition by ppr.seqno
			order by ppa.seqno
			)as [First Activity]
		--,ppa.SEQNO as [Activity ID]
		,(convert(Char(10),ppa.ActivityStartDate,103)) AS [Activity Start Date]
		,ppas.LocalShortCode as [Activity Setting Code]
		,ppat.LocalShortCode as [Activity Type Code]
		,datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) as [Wait Days]
		,case
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) =0
			then '0 - No Wait'
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) between 1 and 21
			then '1-21 days'
			when datediff(d,ppr.ReferralStartDate,ppa.ActivityStartDate) between 22 and 56
			then '22-56 days'
			else '>=56 days'
			end as [Wait Time Days]
	from
			patient_primhd_referral as ppr
			FULL outer join PATIENT_PRIMHD_ACTIVITY AS ppa
			on ppr.SEQNO=ppa.REFERRAL_SEQNO
			full outer join  patient_details as pd
			on ppa.PATIENT_ID=pd.PATIENT_ID
			full outer join  PATIENT_ETH_TYPES as pet
			on pet.SEQNO=pd.ETHNICITY
			full outer join PATIENT_PRIMHD_FROMCODE as ppf
			ON ppr.ReferralFromID=ppf.SEQNO
			full outer join PATIENT_PRIMHD_TEAM_DETAILS as pptd
			on ppa.ReferralTeamID=pptd.SEQNO
			full outer join PATIENT_PRIMHD_ACTIVITY_TYPE AS ppat
			on ppa.ActivityTypeCodeID=ppat.SEQNO
			full outer join PATIENT_PRIMHD_ENDCODE AS PPE
			on ppr.ReferralEndCodeID=ppe.SEQNO
			full outer join PATIENT_PRIMHD_ACTIVITY_SETTING as ppas
			on ppa.ActivitySettingID=ppas.SEQNO
where
	--Referral Start date is between PMR period
	ppr.ReferralStartDate >=@StartDate
	and ppr.ReferralStartDate <@EndDate
	--Team Codes 010071, 7697 lookup on pptd.seqno
	and pptd.SEQNO in (25,29)
	--Activity Type Code excludes T08, T33, T35, T37
	and ppat.LocalShortcode not in ('T08','T08 173','T08 174','T08 175','T08 179','T33','T35','T37')
	--Activity Setting Code excludes WR, SM, PH, OM
	and ppas.LocalShortCode not in ('WR', 'SM','PH','OM')
	--Test for Data from DHB
	--and ppr.SEQNO in (6727,7096)
	) as r
where r.[First Activity] = 1
group by
	[Wait Time Days]
	,[Age Group]
	,[Referral ID]

) as f
pivot(count([Referral ID]) FOR [Age Group] IN (
			[0-19]
			,[20-64]
			,[65+]
			)) AS p
group by rollup(
	[Wait Time Days]
	,[Age Group]
	,[Referral ID]
	,[Wait Days])
;

BUMP - can anyone help me on this, or recommend other forums I can post this on?

The thing do to is restrict the source for the pivot to just those columns needed for the pivot. I can see that you have much more than that. So

Select col1, val1, v1l2, val3
from (select col1, col2, col3 from sourcetable) as s
pivot (sum(col2) for col3 in (val1, val2, val3)) p

just to compute the pivot. Then use that as a subquery (or cte if you prefer) and join with the rest of the data to get your final result.