Counting how many times a value appears in a column and assigning an incremental number to each one.

  • Hello I'm new to SQL. I'm getting the hang of it, but still hit snags from here to there.

    I have a database with multiple items in a column. I want to know how many times each value appears in the column AND I want to assign a number to those values. The number must start at 0

    We'll use dogs as an example:

    Table

    Dog Breed

    Boxer

    Boxer

    Boxer

    Boxer

    Yorkie

    Yorkie

    Poodle

    Poodle

    Poodle

    Result

    Dog Breed How Many Order

    Boxer 4 0

    Boxer 4 1

    Boxer 4 2

    Boxer 4 3

    Yorkie 2 0

    Yorkie 2 1

    Poodle 3 0

    Poodle 3 1

    Poodle 3 2

    Thanks so much for your help!

  • hamptonmom2009 (8/19/2014)


    Hello I'm new to SQL. I'm getting the hang of it, but still hit snags from here to there.

    I have a database with multiple items in a column. I want to know how many times each value appears in the column AND I want to assign a number to those values. The number must start at 0

    We'll use dogs as an example:

    Table

    Dog Breed

    Boxer

    Boxer

    Boxer

    Boxer

    Yorkie

    Yorkie

    Poodle

    Poodle

    Poodle

    Result

    Dog Breed How Many Order

    Boxer 4 0

    Boxer 4 1

    Boxer 4 2

    Boxer 4 3

    Yorkie 2 0

    Yorkie 2 1

    Poodle 3 0

    Poodle 3 1

    Poodle 3 2

    Thanks so much for your help!

    Something like:

    WITH cte AS (SELECT DogBreed, COUNT(*) as HowMany FROM myTable)

    SELECT a.DogBreed, b.HowMany, ROW_NUMBER() (PARTITION BY a.DogBreed ORDER BY a.DogBreed) AS cnt

    FROM myTable a

    INNER JOIN cte b ON a.DogBreed = b.DogBreed;

    This does not start at zero but you can work on that.

  • Thanks a lot! I'll try it.

    Can you explain the cte?

  • djj (8/19/2014)


    Something like:

    WITH cte AS (SELECT DogBreed, COUNT(*) as HowMany FROM myTable)

    SELECT a.DogBreed, b.HowMany, ROW_NUMBER() (PARTITION BY a.DogBreed ORDER BY a.DogBreed) AS cnt

    FROM myTable a

    INNER JOIN cte b ON a.DogBreed = b.DogBreed;

    This does not start at zero but you can work on that.

    That won't quite work. There is no GROUP BY in your cte's aggregate. Honestly you don't really need a cte for this anyway.

    This should work just fine.

    select DogBreed

    , COUNT(*)

    , ROW_NUMBER() over(partition by DogBreed order by newid()) - 1 as MyOrder

    from SomeTable

    group by DogBreed

    Doh!!! The code I posted won't work either. Missed an important piece of info. :hehe:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • hamptonmom2009 (8/19/2014)


    Hello I'm new to SQL. I'm getting the hang of it, but still hit snags from here to there.

    I have a database with multiple items in a column. I want to know how many times each value appears in the column AND I want to assign a number to those values. The number must start at 0

    We'll use dogs as an example:

    Table

    Dog Breed

    Boxer

    Boxer

    Boxer

    Boxer

    Yorkie

    Yorkie

    Poodle

    Poodle

    Poodle

    Result

    Dog Breed How Many Order

    Boxer 4 0

    Boxer 4 1

    Boxer 4 2

    Boxer 4 3

    Yorkie 2 0

    Yorkie 2 1

    Poodle 3 0

    Poodle 3 1

    Poodle 3 2

    Thanks so much for your help!

    Here's an approach that uses the CTE to build the counts and then another one to pull the number for each one. The second CTE is just there so we can subtract one from the ROW_NUMBER for display.

    with counts as (

    select breed, COUNT(*) row_count

    from #dogs

    group by breed

    ),

    results as (

    select d.breed, counts.row_count, ROW_NUMBER() over(partition by d.breed order by d.breed) row_num

    from #dogs d

    inner join counts on counts.breed = d.breed

    )

    select breed, row_count, row_num - 1

    from results

    order by breed, row_num;

    I'm curious why you want the count along with a numbered list, but that's a separate issue.

  • Here is yet another way. I have a view in my system called cteTally. It is nothing but a view that has the numbers 1 - 10,000. Makes this kind of thing rather easy. We can use this approach as an alternative to using ROW_NUMBER. Not saying one is better than the other, this is just another option.

    WITH cte AS (SELECT DogBreed, COUNT(*) as HowMany FROM SomeTable group by DogBreed)

    SELECT cte.*, t.N - 1

    FROM cte

    join cteTally t on t.N <= cte.HowMany

    order by DogBreed, t.N

    Here is an article that explains how you can use a tally or numbers table. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

    Using those concepts I created the following view. This ends up being a zero read query that will produce 10,000 rows numbered and in sequence. 😉

    create View [dbo].[cteTally] as

    WITH

    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    )

    select N from cteTally

    GO

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Why so much extra work? No CTEs required:

    create table dbo.DogBreeds (

    DogBreed varchar(32)

    );

    insert into dbo.DogBreeds

    values

    ('Boxer'),

    ('Boxer'),

    ('Boxer'),

    ('Boxer'),

    ('Yorkie'),

    ('Yorkie'),

    ('Poodle'),

    ('Poodle'),

    ('Poodle');

    go

    select

    DogBreed,

    count(*) over (partition by DogBreed) Cnt,

    row_number() over (partition by DogBreed order by (select null)) - 1 Rn

    from

    dbo.DogBreeds;

    go

    drop table dbo.DogBreeds;

    go

  • Thanks everyone!

    I took bits and pieces. I realized I needed to add the dog's names to make things unique. I was able to get what I wanted (minus the count which isn't as important as the row number) with:

    SELECT

    Dog_names,

    Dog_breed,

    ROW_NUMBER() OVER(

    PARTITION BY

    Dog_breed) as order

    FROM

    mydogtable

    Group By

    Dog_names,

    Dog_breed

    The trouble is now I can't get the row number to start with '0' rather than '1'.

    Dog_names Dog_breed Order

    Princess Boxer 1

    Jack Boxer 2

    Charlie Boxer 3

    Hercules Boxer 4

    Biggie Yorkie 1

    Bruno Yorkie 2

    kipper Poodle 1

    Sandy Poodle 2

    Marley Poodle 3

    SSCarpal Tunnel, I was just adding the count because this will be a table used for various reasons. Sometimes the users will need to know how many of each breed and other times the users will need to pick, the boxer for example, by the order they are listed.

    Lynn, yours looks simple, but I have about 305K lines. 🙂

  • hamptonmom2009 (8/19/2014)


    Thanks everyone!

    I took bits and pieces. I realized I needed to add the dog's names to make things unique. I was able to get what I wanted (minus the count which isn't as important as the row number) with:

    SELECT

    Dog_names,

    Dog_breed,

    ROW_NUMBER() OVER(

    PARTITION BY

    Dog_breed) as order

    FROM

    mydogtable

    Group By

    Dog_names,

    Dog_breed

    The trouble is now I can't get the row number to start with '0' rather than '1'.

    Dog_names Dog_breed Order

    Princess Boxer 1

    Jack Boxer 2

    Charlie Boxer 3

    Hercules Boxer 4

    Biggie Yorkie 1

    Bruno Yorkie 2

    kipper Poodle 1

    Sandy Poodle 2

    Marley Poodle 3

    SSCarpal Tunnel, I was just adding the count because this will be a table used for various reasons. Sometimes the users will need to know how many of each breed and other times the users will need to pick, the boxer for example, by the order they are listed.

    Lynn, yours looks simple, but I have about 305K lines. 🙂

    Try this:

    SELECT

    Dog_names,

    Dog_breed,

    ROW_NUMBER() OVER(

    PARTITION BY

    Dog_breed) - 1 as order

    FROM

    mydogtable

    Group By

    Dog_names,

    Dog_breed

  • hamptonmom2009 (8/19/2014)


    I have about 305K lines. 🙂

    Why is that a problem? Or it isn't?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • hamptonmom2009 (8/19/2014)


    Thanks everyone!

    I took bits and pieces. I realized I needed to add the dog's names to make things unique. I was able to get what I wanted (minus the count which isn't as important as the row number) with:

    SELECT

    Dog_names,

    Dog_breed,

    ROW_NUMBER() OVER(

    PARTITION BY

    Dog_breed) as order

    FROM

    mydogtable

    Group By

    Dog_names,

    Dog_breed

    The trouble is now I can't get the row number to start with '0' rather than '1'.

    Dog_names Dog_breed Order

    Princess Boxer 1

    Jack Boxer 2

    Charlie Boxer 3

    Hercules Boxer 4

    Biggie Yorkie 1

    Bruno Yorkie 2

    kipper Poodle 1

    Sandy Poodle 2

    Marley Poodle 3

    SSCarpal Tunnel, I was just adding the count because this will be a table used for various reasons. Sometimes the users will need to know how many of each breed and other times the users will need to pick, the boxer for example, by the order they are listed.

    Lynn, yours looks simple, but I have about 305K lines. 🙂

    Looking at the comment about 305K lines, I hope you didn't assume that you would have to enter all your data into the table I created to write my code. It was nothing more than a sample table and data. You would replace my table in my code with your table.

    Also, you really don't need the group by with the windowing function ROW_NUMBER() OVER ...

    Also, I would have expected you to get an error message since the ROW_NUMBER() OVER doesn't have an ORDER BY.

  • Just to show how it still works with the dogs names included:

    create table dbo.DogBreeds_TestTable (

    DogName varchar(32),

    DogBreed varchar(32)

    );

    --Dog_names Dog_breed Order

    --Princess Boxer 1

    --Jack Boxer 2

    --Charlie Boxer 3

    --Hercules Boxer 4

    --Biggie Yorkie 1

    --Bruno Yorkie 2

    --kipper Poodle 1

    --Sandy Poodle 2

    --Marley Poodle 3

    insert into dbo.DogBreeds_TestTable

    values

    ('Princess','Boxer'),

    ('Jack','Boxer'),

    ('Charlie','Boxer'),

    ('Hercules','Boxer'),

    ('Biggie','Yorkie'),

    ('Bruno','Yorkie'),

    ('kipper','Poodle'),

    ('Sandy','Poodle'),

    ('Marley','Poodle');

    go

    select

    DogName,

    DogBreed,

    count(*) over (partition by DogBreed) Cnt,

    row_number() over (partition by DogBreed order by (select null)) - 1 Rn

    from

    dbo.DogBreeds_TestTable;

    go

    drop table dbo.DogBreeds_TestTable;

    go

    Please note, you would substitute your table for dbo.DogBreeds_TestTable.

  • This is one of the great things about this site. A question gets posted and several working solutions get posted. Then we can all learn something from the different ways.

    Lynn, I learned about the COUNT(*) OVER from your post. I tried it and it worked, so I looked it up on MSDN at http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx. The 2008 and 2012 pages don't have the OVER clause at all, but the 2014 does. It does work in SQL 2008 SP3. Thanks for posting it because I learned something new, which is always a great way to start the day.

  • Quick question, what SQL Server Version are you on (SELECT @@VERSION)?

    😎

  • Eirikur Eiriksson (8/20/2014)


    Quick question, what SQL Server Version are you on (SELECT @@VERSION)?

    😎

    SQL 2008 SP3 64-bit Standard Edition running on Windows Server 2008 Enterprise.

Viewing 15 posts - 1 through 15 (of 23 total)

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