Post reply

GROUING function

  • 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

  • April 10, 2003 at 8:17 pm

    #453823

    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


    Darren

  • April 11, 2003 at 2:44 am

    #453843

    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.

  • April 11, 2003 at 11:33 am

    #453926

    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

  • April 11, 2003 at 12:37 pm

    #453930

    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

  • April 12, 2003 at 11:46 am

    #453982

    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


    Darren

  • April 14, 2003 at 2:27 am

    #454015

    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.

  • April 25, 2003 at 6:45 am

    #455374

    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.

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

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