Post reply

with rollup|cube

  • September 21, 2005 at 1:33 pm

    #87834

    It is sometimes useful to use 'with rollup|cube'  in group by clause, but I am frustrated with the summary line having null value in the group by columns. Quite often I have real null value in the columns, and I have to spend time to determine the real null value and the null in the summary role. Is there a quick way to turn the null in summary role to things like 'TOTOL' or 'ALL' ?

    Thanks

     

     

  • September 21, 2005 at 2:47 pm

    #592017

    Yes! Take a look at

    GROUPING ( column_name )

     in BOL.

    Is exactly what you need to either Filter them out or simply change that Null for 'TOTAL' or ALL

     


    * Noel

  • September 22, 2005 at 8:06 am

    #592217

    This is how you handle (source BOL):

    GROUPING

    Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

    Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

    Syntax

    GROUPING ( column_name )

    Arguments

    column_name

    Is a column in a GROUP BY clause to check for CUBE or ROLLUP null values.

    Return Types

    int

    Remarks

    Grouping is used to distinguish the null values returned by CUBE and ROLLUP from standard null values. The NULL returned as the result of a CUBE or ROLLUP operation is a special use of NULL. It acts as a column placeholder in the result set and means "all."

    Examples

    This example groups royalty and aggregate advance amounts. The GROUPING function is applied to the royalty column.

    USE pubsSELECT royalty, SUM(advance) 'total advance',    GROUPING(royalty) 'grp'   FROM titles   GROUP BY royalty WITH ROLLUP

    The result set shows two null values under royalty. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the total advance amounts for all royalty groups and is indicated by 1 in the grp column.

    Here is the result set:

    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

  • September 22, 2005 at 8:08 am

    #592219

    sorry, I didn't realize the blog exceed the limit. The example is under this topic 'Summarizing Data Using ROLLUP'.

Viewing 4 posts - 1 through 3 (of 3 total)

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