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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-30 : 09:29:56
|
| Brian writes "Hi, I have created (using ASP) a front-end that lets people query a single sql-server table. They get to choose the data they want, and how they would like that data grouped. My problem is how to display the detailed data (not aggregate)as well as a count of records for each group i.e.detail record 1detail record 2detail record 3 total: 3detail record 4detail record 5 total: 2 I've played around with the 'compute' and 'rollup' statements, but can't seem to get them to work right. They either give me only aggregate data, or they give me details and aggregate data, but in separate recordsets. Would I be better off using sub-querries? If so how? Help! Thanks in advance! (BTW, I'm using SQL-Server 2000 on Windows 2000)Brian" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-30 : 10:02:31
|
| ROLLUP is probably the best one for you, but you need to work with the GROUPING function to eliminate the sub-totals you don't want. For example:SELECT A, B, Count(*) AS C FROM myTable GROUP BY A, B WITH ROLLUPThis will create subtotals for A AND B, but I only want the totals for the A groups. This will do it:SELECT A, B, Count(*) AS C FROM myTable GROUP BY A, B WITH ROLLUP HAVING GROUPING(A)=1Books Online has some examples of the GROUPING function in action, and if you play with it a little I'm sure you'll be able to get only the data you want. It will be much more efficient and easier to use than subqueries. |
 |
|
|
|
|
|
|
|
detailed and summary records in the same recordset