August 19, 2014 at 1:03 pm
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!
August 19, 2014 at 1:51 pm
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.
August 19, 2014 at 1:52 pm
Thanks a lot! I'll try it.
Can you explain the cte?
August 19, 2014 at 1:53 pm
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/
August 19, 2014 at 1:54 pm
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.
August 19, 2014 at 2:07 pm
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/
August 19, 2014 at 2:08 pm
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
August 19, 2014 at 2:45 pm
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. 🙂
August 19, 2014 at 2:58 pm
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
August 19, 2014 at 3:20 pm
hamptonmom2009 (8/19/2014)
I have about 305K lines. 🙂
Why is that a problem? Or it isn't?
August 19, 2014 at 3:32 pm
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.
August 19, 2014 at 5:58 pm
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.
August 20, 2014 at 5:20 am
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.
August 20, 2014 at 5:54 am
Quick question, what SQL Server Version are you on (SELECT @@VERSION)?
😎
August 20, 2014 at 5:58 am
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