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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy