Reporting: Summary Data in Detail View

Ever need to have a summary column for the whole record set in each line of a detail record set? If so, here is a way to provide that in a view or report.


DECLARE @TblDetail TABLE
(DetId INT Identity (1,1), SumID INT, Descr Char(2), Num SmallInt)
DECLARE @TblSum TABLE
(SumId INT Identity (1,1) , Descr Char(2))
INSERT INTO @TblSum
(Descr)
VALUES
('AA'),
('BB')
INSERT INTO @TblDetail
(SumID, Descr, Num)
VALUES
(1,'AB',2),
(1,'AC',32),
(2,'CA',2);
WITH SumDetail (SumID, SumNum)
AS(
SELECT
ts.SumID,
SUM(td.num)
FROM @TblSum ts
join @TblDetail td on ts.Sumid = td.sumid
GROUP BY
ts.SumID)
SELECT
td.*,
sd.SumNum Summary
FROM @TblSum ts
join @TblDetail td on ts.Sumid = td.sumid
join SumDetail sd on sd.sumid = td.sumid