question

I am experimenting WITH ROLLUP. I am running SQL Server 2008R2. Attached is my stored procedure reporting on data from the previous year (2013) and a few lines of the output. It is reporting on the total number of items in each collection for a library. I am certain when the end-user sees this report they are going to complain that the report is producing duplicate records; Is there a way I can remove the duplicate records, which is actually a subtotal, in the stored procedure or Visual Studio before the report is rendered in EXCEL for the user? Thank you. Query: select ISNULL(torg.name, CASE WHEN GROUPING (torg.name) = 0 THEN ' Unknown' ELSE 'Total ALL Branches' End) as Branch, ISNULL(polcol.name, CASE WHEN GROUPING (polcol.name) = 0 THEN '(none)' ELSE 'Total ALL Collections' End) as Collection, COUNT(*) AS TotalCircs from PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK) left outer join PolarisTransactions.Polaris.TransactionDetails td (nolock) on (th.TransactionID = td.TransactionID) inner join Polaris.Polaris.Organizations torg (nolock) on (th.OrganizationID = torg.OrganizationID) left join Polaris.Polaris.Collections polcol with (nolock) on (td.numvalue = polcol.CollectionID) where th.TransactionTypeID = 6001 and td.TransactionSubTypeID = 61 --AssignedColletionCode and th.TranClientDate between @dtStartDate and @dtEndDate and th.OrganizationID in (7,8,9,10,11,12,13,5,14,15,16,17,4,18,19,20,21,22,23,24,6,3,2,25,26,27) Group by ROLLUP (torg.name, polcol.name) Order by torg.name,polcol.name Output: There are 24 entries for items without a Collection, one for each month. Total ALL Branches Total ALL Collections 8338728 Arlington Library (none) 73 Arlington Library (none) 73 Arlington Library (none) 31 Arlington Library (none) 31 Arlington Library (none) 50 Arlington Library (none) 50 Arlington Library (none) 44 Arlington Library (none) 44 Arlington Library (none) 55 Arlington Library (none) 55 Arlington Library (none) 34 Arlington Library (none) 34 Arlington Library (none) 37 Arlington Library (none) 37 Arlington Library (none) 54 Arlington Library (none) 54 Arlington Library (none) 74 Arlington Library (none) 74 Arlington Library (none) 51 Arlington Library (none) 51 Arlington Library (none) 9 Arlington Library (none) 9 Arlington Library (none) 8 Arlington Library (none) 8 Arlington Library (none) 520