GROUING function

Question

GROUING function

danv

Mr or Mrs. 500

Points: 583
More actions
April 10, 2003 at 10:02 am

#81588

Could someone point me in the direction of a good expl/tut of using/exploiting GROUPING programmtically. For once BOL doesn't cut it.
TIA all

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dgermundson Hall of Fame Points: 3175 More actions · Answer 1

What exactly are you trying to do? Look at "Summary and Grouping Behavior in the Query Designer" in books online. Is that what you are looking for?

Darren

danv Mr or Mrs. 500 Points: 583 More actions · Answer 2

I'm looking the GROUPING function applied to the GROUP BY clause of my built T-SQL.

I just need a better discussion of it than BOL has - kinda helpful but not really. Some real world examples or something...

thanks.

jpipes SSChampion Points: 12230 More actions · Answer 3

Unless you state what it is you are trying to do with your SQL, it's tough to point you in the right direction. Darren was right on with his search criteria. The url you want to use and read through is:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvovrqueryingongroupsofrows.asp

Edited by - jpipes on 04/11/2003 11:35:12 AM

SJTerrill SSCrazy Points: 2938 More actions · Answer 4

Zarathustra, I think I see your point. I checked the link and it discusses GROUP BY, not the GROUPING keyword. Perhaps GROUPING is useful in situations where you want to get the aggregate results from your GROUP BY and further GROUPING aggregates in the same SELECT statement. From BOL's example:

 

USE pubs

SELECT royalty, SUM(advance) 'total advance',

   GROUPING(royalty) 'grp'

   FROM titles

   GROUP BY royalty WITH ROLLUP

Results:



royalty     total advance         grp  

----------- --------------------- ---- 

NULL        NULL                  0

10          57000.0000            0

12          2275.0000             0

14          4000.0000             0

16          7000.0000             0

24          25125.0000            0

NULL        95400.0000            1



Warning: Null value eliminated from aggregate.

The grp value indicates the GROUPING aggregate, which could be used to qualify report formatting or somesuch. A better example might use something along the lines of Sales Region and ProductID columns from a tblSaleDetails table with a GROUPING(Region). You CAN add ORDER BY GROUPING(Region) DESC to get the Region summaries at the top of your results.

Something tells me I haven't clarified this ,

SJTerrill

dgermundson Hall of Fame Points: 3175 More actions · Answer 5

I see the grouping as something that would benefit scripts or programs that access the result set. The grouping column in the example would allow you to format a report with the details and then the totals would be the ones with the 1 in the grouping column. So looping through the recordset would be easier to program because you would have 1 field to tell you what rows are subtotals or totals. Hope this helps.

Darren

danv Mr or Mrs. 500 Points: 583 More actions · Answer 6

TheWildHun,

That's is pretty much exactly what I am doing. It's for report formatting exactly, I'm using it to break out the different levels of aggregation applied by WITH ROLLUP. It's a bit of a pet project to build a more flexible reporting tool. As dgermudson pointed out it really helps scripts which are accessing the resultset. Sorry though - I should have been more specific with my syntax.

On the same note: what do any of you recommend as a very thorough reference for T-SQL development? A cookbook kind of reference or something like it.

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 7

The grouping tells you if the row is an aggregate using that column. Try using it in the pubs database, grouping by country, state, city, WITH CUBE to see its use. It is like creating a flat file of the OLAP cubes.