locked
Rollup values to a certain level RRS feed

  • Question

  • Hi,

    I'm using SQL Server 2005.

    Here's my TSQL and the results I get when I execute it:

    select ReadDays as TotalDays, count(*) as ReportsRead
    from turnaround
    where DOS between @StartDate and @EndDate and ReadDays is not null and LocationsId is not Null
    group by ReadDays with rollup
    
    
    TotalDays  ReportsRead
    ----------- -----------
    0      81
    1      1347
    2      692
    3      305
    4      61
    5      28
    6      11
    7      10
    8      12
    9      4
    11     2
    13     1
    15     1
    NULL    2555
    

    I would like to group all the values >= 12 together so that instead of getting a 13 and 15 I just get 12+ = 2.

    I'd like the result to look like this:

    TotalDays  ReportsRead
    ----------- -----------
    0      81
    1      1347
    2      692
    3      305
    4      61
    5      28
    6      11
    7      10
    8      12
    9      4
    11     2
    12+     2
    NULL    2555
    

    I've tried to do a CASE ReadDays >=12 THEN '12+' ELSE CONVERT(nvarchar(3), ReadDays) END in my SQL statement but then I get 2 entries for '12+' instead of one entry.

    Any ideas how I can group the values greater than or equal to 12 together in the rollup?

    Thanks,

    -- John...

     

    Thursday, October 14, 2010 10:57 PM

Answers

  • Include column [ReadDays] in the CTE and order by the MIN or MAX of this column.

    USE tempdb;
    GO
    SET NOCOUNT ON;
    GO
    DECLARE @T TABLE (c1 int NOT NULL, c2 int NOT NULL);
    
    INSERT INTO @T VALUES(0, 81);
    INSERT INTO @T VALUES(1, 1347);
    INSERT INTO @T VALUES(2, 692);
    INSERT INTO @T VALUES(3, 305);
    INSERT INTO @T VALUES(4, 61);
    INSERT INTO @T VALUES(5, 28);
    INSERT INTO @T VALUES(6, 11);
    INSERT INTO @T VALUES(7, 10);
    INSERT INTO @T VALUES(8, 12);
    INSERT INTO @T VALUES(9, 4);
    INSERT INTO @T VALUES(11, 2);
    INSERT INTO @T VALUES(13, 1);
    INSERT INTO @T VALUES(15, 1);
    
    WITH rs AS (
    SELECT
      CASE WHEN c1 >= 12 THEN '12+' ELSE CAST(c1 as varchar(15)) END as rd,
      c1,
      c2
    from
      @T
    )
    SELECT rd AS TotalDays, SUM(c2) AS ReportsRead
    FROM rs
    GROUP BY rd WITH ROLLUP
    ORDER BY
      GROUPING(rd),
      MAX(c1);
    GO
    SET NOCOUNT OFF;
    GO
    

    AMB

    Some guidelines for posting questions...

    • Proposed as answer by David Frommer Saturday, October 16, 2010 6:25 AM
    • Marked as answer by Ai-hua Qiu Tuesday, October 26, 2010 5:54 AM
    Friday, October 15, 2010 6:09 PM

All replies

  • Use derived table, a cte, or a view to assign same identifier to those values.

    ;with rs as (
    select
        case when readDays >= 12 then '12+' else cast(readDays as varchar(15)) as rd
    from
        turnaround
    where
        DOS between @StartDate and @EndDate and ReadDays is not null and LocationsId is not Null
    )
    select rd as TotalDays, count(*) as ReportsRead
    from rs
    group by rd with rollup;

     

     


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by K H Tan Thursday, October 14, 2010 11:40 PM
    Thursday, October 14, 2010 11:33 PM
  • Your solution is close but I have one little problem.  I need to have the items ordered correctly.  Since I'm converting to varchar the values are not ordered correctly.

    What I get is:

    TotalDays    ReportsRead
    --------------- -----------
    0        81
    1        1347
    11       2
    12+       2
    2        692
    3        305
    4        61
    5        28
    6        11
    7        10
    8        12
    9        4
    NULL      2555
    

    How can I order this so I get 11 and 12+ at the bottom of the list (before the NULL)? 

    Friday, October 15, 2010 5:58 PM
  • Add replicate on hunchback's query:

    select replicate('0',2-len(rd)) + rd as TotalDays, count(*) as ReportsRead
    from rs
    group by rd with rollup;
    


    ~Manu
    http://sqlwithmanoj.blogspot.com/
    Friday, October 15, 2010 6:09 PM
  • Include column [ReadDays] in the CTE and order by the MIN or MAX of this column.

    USE tempdb;
    GO
    SET NOCOUNT ON;
    GO
    DECLARE @T TABLE (c1 int NOT NULL, c2 int NOT NULL);
    
    INSERT INTO @T VALUES(0, 81);
    INSERT INTO @T VALUES(1, 1347);
    INSERT INTO @T VALUES(2, 692);
    INSERT INTO @T VALUES(3, 305);
    INSERT INTO @T VALUES(4, 61);
    INSERT INTO @T VALUES(5, 28);
    INSERT INTO @T VALUES(6, 11);
    INSERT INTO @T VALUES(7, 10);
    INSERT INTO @T VALUES(8, 12);
    INSERT INTO @T VALUES(9, 4);
    INSERT INTO @T VALUES(11, 2);
    INSERT INTO @T VALUES(13, 1);
    INSERT INTO @T VALUES(15, 1);
    
    WITH rs AS (
    SELECT
      CASE WHEN c1 >= 12 THEN '12+' ELSE CAST(c1 as varchar(15)) END as rd,
      c1,
      c2
    from
      @T
    )
    SELECT rd AS TotalDays, SUM(c2) AS ReportsRead
    FROM rs
    GROUP BY rd WITH ROLLUP
    ORDER BY
      GROUPING(rd),
      MAX(c1);
    GO
    SET NOCOUNT OFF;
    GO
    

    AMB

    Some guidelines for posting questions...

    • Proposed as answer by David Frommer Saturday, October 16, 2010 6:25 AM
    • Marked as answer by Ai-hua Qiu Tuesday, October 26, 2010 5:54 AM
    Friday, October 15, 2010 6:09 PM