group by continual

  • Hello,

    Let's say I have a table with 2 columns, id and name, where I insert 4 rows:

    id name

    1 John

    2 Mary

    3 John

    4 John

    and I want to copy the rows to a new table that has columns name and nameCount where I will put the number of continual names and only one row for each such name:

    name nameCount

    John 1

    Mary 1

    John 2

    How do I code the TSQL?

    Thanks!

  • depends on what version of SQL Server you're using. In 2012, it's easy:

    SELECT personID

    , Name

    , COUNT(*) OVER (PARTITION BY Name ORDER BY personID ROWS UNBOUNDED PRECEDING) AS NameCount

    FROM Person

    ORDER BY PersonID;

    but those weren't introduced until 2012... so if you're using an earlier version, you will need something like this:

    SELECT Name

    , PersonID

    , ( SELECT COUNT(PersonID)

    FROM Person P2

    WHERE P2.PersonID<=P1.PersonID

    AND P2.Name = P1.Name ) AS RunningCount

    FROM Person P1;

    (I can hear Jeff Moden's "triangular join" sirens going off right now... )

    HTH,

    Pieter

  • You are right to assume I have an older version (2005 🙁 ). However, I guess I was too short in my sample as this is not the result I wanted. I want to count the occurrence of a name in a series. Each series for a name is completely independent from another series of that same name. Here is a more elaborate version:

    Source table:

    id name

    1 John

    2 Mary

    3 Mary

    4 Mary

    5 John

    6 John

    7 Mary

    Resulting in:

    name nameCount

    John 1

    Mary 3

    John 2

    Mary 1

  • SeeknFind (10/29/2015)


    You are right to assume I have an older version (2005 🙁 ). However, I guess I was too short in my sample as this is not the result I wanted. I want to count the occurrence of a name in a series. Each series for a name is completely independent from another series of that same name. Here is a more elaborate version:

    Source table:

    id name

    1 John

    2 Mary

    3 Mary

    4 Mary

    5 John

    6 John

    7 Mary

    Resulting in:

    name nameCount

    John 1

    Mary 3

    John 2

    Mary 1

    Please see the first link in my signature line for all future posts. Thanks.

    The following will do as you ask. Details in the comments. This assumes that ID is truly sequential. There's an easy fix if not.

    --===== This just creates some test data and is NOT a part of the solution

    SELECT *

    INTO #MyHead

    FROM (

    SELECT 1,'John' UNION ALL

    SELECT 2,'Mary' UNION ALL

    SELECT 3,'Mary' UNION ALL

    SELECT 4,'Mary' UNION ALL

    SELECT 5,'John' UNION ALL

    SELECT 6,'John' UNION ALL

    SELECT 7,'Mary'

    ) d (ID,Name)

    ;

    --===== One possible solution WITHOUT exposed sort order

    WITH cteGrouping AS

    (

    SELECT ID

    ,Name

    ,MyGroup = ID-ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY ID)

    FROM #MyHead

    )

    SELECT Name

    ,ConsecutiveRun = COUNT(*)

    FROM cteGrouping

    GROUP BY Name,MyGroup

    ORDER BY MIN(ID)

    ;

    --===== One possible solution WITH exposed sort order

    WITH cteGrouping AS

    (

    SELECT ID

    ,Name

    ,MyGroup = ID-ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY ID)

    FROM #MyHead

    )

    SELECT MinID = MIN(ID)

    ,Name

    ,ConsecutiveRun = COUNT(*)

    FROM cteGrouping

    GROUP BY Name,MyGroup

    ORDER BY MinID

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Great. I realized that this is not really about CTE's. It is a smart way of taking advantage of the number sequences. Thanks!

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

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