April 28, 2017 at 8:35 am
Any idea how can i get a running count in sql?
This is my desired result:
| Date | Name | Count |
| 1/1/2017 | A | 1 |
| 1/2/2017 | B | 1 |
| 1/3/2017 | C | 1 |
| 1/4/2017 | D | 1 |
| 1/5/2017 | A | 2 |
| 1/6/2017 | C | 2 |
| 1/7/2017 | C | 3 |
| 1/8/2017 | B | 2 |
| 1/9/2017 | D | 2 |
| 1/10/2017 | A | 3 |
Count Column is basically running count by Name.
This is what i got so far:
Select Date, Name, (select count(*) +1 from table where date < b.date) as Count
from table as b
where b.date between '1/1/2017' to '1/10/2017'
order by Date, Name
Thanks in advance!
April 28, 2017 at 8:41 am
in newer versions of SQL Server you could probably use windowing functions with your count:
http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1
April 28, 2017 at 8:56 am
For something that doesn't use Qurky, you could use something like a CROSS APPLY, at least it doesn't run in the SELECT statement, but does it as a dataset. i've also included an example of using the OVER clause for you though.CREATE TABLE #Sample
([Date] datetime,
[Name] char(1));
GO
INSERT INTO #Sample
VALUES
('1/1/2017','A'),
('1/2/2017','B'),
('1/3/2017','C'),
('1/4/2017','D'),
('1/5/2017','A'),
('1/6/2017','C'),
('1/7/2017','C'),
('1/8/2017','B'),
('1/9/2017','D'),
('1/10/2017','A');
GO
GO
--SQL 2008 option using CROSS APPLY.
SELECT S1.Date, S1.Name,
s2.[Count] AS [Count]
FROM #Sample S1
CROSS APPLY (SELECT COUNT(*) AS [Count]
FROM #Sample ca
WHERE ca.Date <= S1.Date
AND ca.Name = S1.Name) S2;
GO
--SQL 2012+ using OVER clause
SELECT S1.[Date], S1.[Name],
COUNT(S1.[Name]) OVER (PARTITION BY S1.Name ORDER BY S1.Date
ROWS UNBOUNDED PRECEDING) AS [Count]
FROM #Sample S1
ORDER BY S1.[Date], S1.[Name];
GO
DROP TABLE #Sample;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 28, 2017 at 10:22 am
lhynlen - Friday, April 28, 2017 8:35 AMAny idea how can i get a running count in sql?
This is my desired result:
Date Name Count 1/1/2017 A 1 1/2/2017 B 1 1/3/2017 C 1 1/4/2017 D 1 1/5/2017 A 2 1/6/2017 C 2 1/7/2017 C 3 1/8/2017 B 2 1/9/2017 D 2 1/10/2017 A 3 Count Column is basically running count by Name.
This is what i got so far:
Select Date, Name, (select count(*) +1 from table where date < b.date) as Count
from table as b
where b.date between '1/1/2017' to '1/10/2017'
order by Date, Name
Thanks in advance!
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 28, 2017 at 10:48 am
ChrisM@home - Friday, April 28, 2017 10:22 AMROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date)
Exactly, the simplest way is the best. It's also compatible with 2005.
April 28, 2017 at 11:11 am
ChrisM@home - Friday, April 28, 2017 10:22 AMlhynlen - Friday, April 28, 2017 8:35 AMAny idea how can i get a running count in sql?
This is my desired result:
Date Name Count 1/1/2017 A 1 1/2/2017 B 1 1/3/2017 C 1 1/4/2017 D 1 1/5/2017 A 2 1/6/2017 C 2 1/7/2017 C 3 1/8/2017 B 2 1/9/2017 D 2 1/10/2017 A 3 Count Column is basically running count by Name.
This is what i got so far:
Select Date, Name, (select count(*) +1 from table where date < b.date) as Count
from table as b
where b.date between '1/1/2017' to '1/10/2017'
order by Date, Name
Thanks in advance!ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date)
+100. No "Quirky Update" required here. As Luis says, it also works just fine in all versions since and including 2005..
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply