Running SubTotal with NULLS

Transact-SQL
1

Is there a better way to write this SQL and get the same results?

IF OBJECT_ID('tempdb..#TEMPTable_Main') IS NOT NULL
BEGIN
	DROP TABLE #TEMPTable_Main
END

CREATE TABLE #TEMPTable_Main
(
     My_ID				INT			NOT NULL	IDENTITY(1,1) PRIMARY KEY
   , My_Account			VARCHAR(10) NOT NULL
   , My_Deposit			INT			NOT NULL
)

INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Jan', 10)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Jan', 20)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Jan', 30)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Feb', 40)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Feb', 50)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Feb', 60)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Mar', 70)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Mar', 80)
/*
SELECT * FROM #TEMPTable_Main
*/
SELECT
	  aT1.*
	, aCombine.My_Total_Deposit
FROM #TEMPTable_Main aT1
LEFT OUTER JOIN
(
	SELECT
		  aM1.My_ID
		, aM1.My_Account
		, aS1.My_Total_Deposit
	FROM
	(
		SELECT
			  My_Account
			, MAX(My_ID)			AS My_ID
		FROM #TEMPTable_Main
		GROUP BY My_Account
	) aM1
	INNER JOIN
	(
		SELECT
			  My_Account
			, SUM(My_Deposit)		AS My_Total_Deposit
		FROM #TEMPTable_Main
		GROUP BY My_Account
	) aS1 ON aS1.My_Account = aM1.My_Account
) aCombine ON aCombine.My_ID = aT1.My_ID
2

I have attempted using this which is close but would still like the NULLs until the subTotal.

SELECT
	  aT1.My_ID
	, aT1.My_Account
	, aT1.My_Deposit
	, SUM(aT1.My_Deposit) OVER (PARTITION BY aT1.My_Account ORDER BY aT1.My_ID) AS 'Total'
FROM #TEMPTable_Main aT1
ORDER BY aT1.My_ID
3

How about this? Is there a better way? Otherwise, can someone give me a pat on the back on this? Hahaha!

SELECT
	  aT1.My_ID
	, aT1.My_Account
	, aT1.My_Deposit
	, CASE
		WHEN aT1.My_ID = MAX(aT1.My_ID) OVER (PARTITION BY aT1.My_Account ORDER BY aT1.My_ID
											ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)		--AS 'Max ID'
		THEN SUM(aT1.My_Deposit) OVER (PARTITION BY aT1.My_Account ORDER BY aT1.My_ID)
		END AS 'Total'
FROM #TEMPTable_Main aT1
ORDER BY aT1.My_ID
4

The above is working fine but I am still curious if there is a better approach or write to produce the same results.

I tried using ROLL UP with GROUPING function to see if I could produce the same results. Would this be possible?

5

hi

does this link help ?????

6

I compared below to yours. This had 49 reads vs 78 that yours had, so that;s a little better. I would need to test it on larger scale to tell you if it's better

select mY_ID, My_Account, My_Deposit,
	   case when my_Account <> NextAccount then 
			SUM(My_Deposit) OVER (PARTITION BY My_Account ORDER BY My_ID)
		END AS 'Total'
from (
SELECT
	  aT1.My_ID
	, aT1.My_Account
	, aT1.My_Deposit
	, lead(aT1.My_Account,1,0) over (PARTITION BY aT1.My_Account ORDER BY aT1.My_ID) as 'NextAccount'
FROM #TEMPTable_Main aT1) v
ORDER BY My_ID
1 Like
7

Hi Harish, I tried to get the content in that link to generate similar to what I was seeking but it wasn't successful.

Hi Mike, thank you for doing that. Can you show me how to do the comparison of "49 reads vs 78"? Is that the execution plan?

8

run this in the SSMS window first

set statistics io on

1 Like
9

Mike, where do I see the read after I run "set statistics io on"?

10

Run this, then click on messages tab in results screen

set statistics io on
go

SELECT
	  aT1.*
	, aCombine.My_Total_Deposit
FROM #TEMPTable_Main aT1
LEFT OUTER JOIN
(
	SELECT
		  aM1.My_ID
		, aM1.My_Account
		, aS1.My_Total_Deposit
	FROM
	(
		SELECT
			  My_Account
			, MAX(My_ID)			AS My_ID
		FROM #TEMPTable_Main
		GROUP BY My_Account
	) aM1
	INNER JOIN
	(
		SELECT
			  My_Account
			, SUM(My_Deposit)		AS My_Total_Deposit
		FROM #TEMPTable_Main
		GROUP BY My_Account
	) aS1 ON aS1.My_Account = aM1.My_Account
) aCombine ON aCombine.My_ID = aT1.My_ID
11

Thanks Mike.

This is what I got:

(8 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TEMPTable_Main_____________________________________________________________________________________________________0000000009EE'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I assume the lower the read number, the better it is right? Is there other ways to measure performance? Thanks Mike!

12

IF OBJECT_ID('tempdb..#TEMPTable_Main') IS NOT NULL
BEGIN
DROP TABLE #TEMPTable_Main
END

CREATE TABLE #TEMPTable_Main
(
My_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
, My_Account VARCHAR(10) NOT NULL
, My_Deposit INT NOT NULL
)

INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Jan', 10)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Jan', 20)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Jan', 30)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Feb', 40)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Feb', 50)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Feb', 60)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Mar', 70)
INSERT INTO #TEMPTable_Main (My_Account, My_Deposit) VALUES ('Mar', 80);

with cte as
(
SELECT
sum(My_Deposit) as My_Total_Deposit
,My_Account
from #TEMPTable_Main
group by My_Account

)
,cte2 as
(
select t.*,c.My_Total_Deposit from #TEMPTable_Main t
left outer join cte c
on t.My_Account = c.My_Account
and t.My_ID in (select max(My_ID) from #TEMPTable_Main
group by My_Account)
)
select * from cte2

1 Like