How to Group By all columns but have Rollup only on few columns

[guydebell]

I am new to SQL coding and having trouble grouping and generating sub totals and Grand Totals for a report generated by this query: SELECT ISNULL(CAST(dteAccountingDate AS varchar (20)),'') AS MonthEnd,Payee, ClientName, ISNULL (CAST (Accrual as varchar (20)),'') AS Accrual, ISNULL (CAST (Payment as varchar (20)),'') AS Payment, ISNULL (CAST (OverUnder as varchar (20)),'') AS OverUnder, ISNULL (CAST (Salaries as varchar (20)),'') AS AnnualisedSalaries, ISNULL (CAST (PercentVal as varchar (20)),'') AS Percentage, ISNULL (CAST (CountMem as varchar (20)),'') AS NoMember, ISNULL (CAST (CostPerMem as varchar (20)),'') AS CostPerMember FROM #tmp INNER JOIN tblClientParents ON #tmp.fkiClientParentID = tblClientParents.pkiClientParentID AND Payee NOT IN ('Opening Value','Current Year','Sub Totals','Totals', '','______________','Prior Year Payment') My primary objective is to have results grouped and summaries generated by Payee and ClientName. I tried group by and order by but they don't give me the required results; even tried Compute By. I want to use Group by With Rollup but the problem is i only want to generate summaries of three columns Accrual, Payment and CountMem. Is there a way to Group by all the columns in the select query but have Rollup only on the three columns? Or any other ways to generate summaries on the three columns only? I also tried Group By Rollup, it doesn't work with SQL Server 2008 that i am using. Need some help here please! Thanks in advance

[Niladri_Biswas]

This will help... GROUP BY, CUBE, ROLLUP and SQL SERVER 2005 :)

Niladri Biswas

modified on Tuesday, December 1, 2009 7:59 AM

[guydebell]

Hi Niladri Thanks for the article, it gives me a better understanding of Grouping and Rollup. I applied it by inserting Case Grouping on Payee and ClientName but still didn't yield the desired results. May be i don't grasp it quite clearly, but you see in the example in the article they had two columns (CustomerName and ItemName), so when grouping they just group by the two columns. In my case i have 10 columns and only have to summarise 3 columns (Payment, Accrual, CountMem) and group by Payee and ClientName only. Please help me out here.