Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-03-19 : 15:25:13
|
| [CODE]SELECT CASE WHEN GROUPING(OS) = 1 THEN 'All OSs' ELSE OS END As Os, CASE WHEN GROUPING(Browser)=1 THEN 'All Browsers' ELSE Browser END As Browser , Version As Ver, COUNT(*) As Total, MAX(User_Agent) User_Agent FROM ( SELECT OSMake As OS , BrowserMake As Browser , Version, Browser As User_Agent FROM dbo.LoginData LD WHERE LD.UserID > 0 -- Do not count failed Logins AND (@CourseID IS NULL OR LD.CourseID = @CourseID) AND (@StartDate IS NULL OR LD.LoginDate >= @StartDate) AND (@FinishDate IS NULL OR LD.LoginDate <= @FinishDate) ) A GROUP BY OS, Browser, Version WITH ROLLUP[/CODE]Rollup is "rolling up" the Version , but I only want subtotals on OS and Browser.Tried:WHERE GROUPING(Version) = 0 -- to eliminate the unwanted rows. No luck.Sam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-19 : 21:53:00
|
| Change it to HAVING Grouping(Version)=0. Grouping() is an aggregate function (kind of) like Sum() and Avg() and therefore cannot be used in a WHERE clause. |
 |
|
|
|
|
|
ROLLUP !