For example imagine a table as below
select accountid, createdon from account
73C56F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:47.000
A7C56F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:48.000
B7C56F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:48.000
FBC56F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:49.000
CBC66F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:54.000
87C66F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:53.000
53C76F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:55.000
87C76F61-5FF1-E111-A4F8-005056977FBC 2012-08-28 22:26:56.000
2ED89924-5CFC-E111-A4F8-005056977FBC 2012-09-11 22:01:51.000
C0D79924-5CFC-E111-A4F8-005056977FBC 2012-09-11 22:01:49.000
then in January 2012 the count was 10 accounts with this query
select count(*) from account
Let's say I have 5 new accounts in February 2012, then querying count(*)
in February 2012 returns 15 accounts. If I have 10 new accounts in March 2012, then then querying count(*)
in March 2012 returns 35 accounts in total.
I'd like to get the below results
2012-january-10 accounts
2012-febrary-15 accounts
2012-march- 35 accounts
creation date in table account is date,
but if I do this query
select year(createdon), month(createdon), count(*) quantity
from accounts
group by year(createdon), month(createdon)
I get the below result instead:
2012-january-10 accounts
2012-febrary-5 accounts
2012-march- 20 accounts
then how can I get the first result with a simple query? not loops
You need a rolling total. There're several way to do it in SQL Server(see Calculate a Running Total in SQL Server), but for SQL 2005 simplest(not fastest) way would be self join
with cte as (
select
datename(year, createdon) + '-' + datename(month, createdon) as name,
convert(nvarchar(6), createdon, 112) as grp,
count(*) as cnt
from account
group by
datename(month, createdon), datename(year, createdon),
convert(nvarchar(6), createdon, 112)
)
select
c1.name, sum(c2.cnt) as cnt
from cte as c1
inner join cte as c2 on c2.grp <= c1.grp
group by c1.name
sql fiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With