October 28, 2015 at 5:58 pm
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!
October 28, 2015 at 9:01 pm
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
October 29, 2015 at 5:37 pm
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
October 29, 2015 at 6:05 pm
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
Change is inevitable... Change for the better is not.
October 30, 2015 at 9:36 am
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