CODEWITHSUNDEEP
sqlsql-serversum
csharpdev
csharpdev

Reputation: 13

Query to sum & count

My data is something like this

Count   years
1     2020-08-11
1    2020-07-11
1    2019-09-01
1    2019-08-16
1    2019-05-04
1    2018-06-11

I'm writing a query where I have to find the count of year for eg <= 04 May 2019 ,I need to find the count of all the dates lesser than that date i.e '2019-05-04' count will come as 1 and then add 1 to the count.

I've written the query like this:

with sum_count as(
    select count(*) as 'Counts', years from [practice].[dbo].[People]
    where years<='2019-05-04'
    group by years)

    select sum(Counts) + 1 as Sum
    from  sum_count

Could you please help to do the same for all the dates like for 2020-08-11 the count shall come as 5 and sum a 6

Upvotes: 0

Views: 115

Answers (3)

Aldrin Machado
Aldrin Machado

Reputation: 97

Writing procedure can be one way, but if your hell bent on using a query you can use this.

select count(1)+1 as sum,a.year1
from
    (
    select distinct year as year1
    from [practice].[dbo].[People]
    
    ) a 
    inner join 
    [practice].[dbo].[People] b on b.year<=a.year1
    group by a.year1

Cheers!!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If I understand correctly, you simply want a window function. The following enumerates each row within each year:

select p.*,
       row_number() over (partition by year(years) order by years) as seqnum
from [practice].[dbo].[People] p;

No stored procedure or auxiliary function is necessary.

Upvotes: 0

user7415753
user7415753

Reputation: 198

You could achieve this quite simply by a select statement without the need for a cte:

Declare @td datetime = '20190504' 
SELECT COUNT([years])+1 FROM [practice].[dbo].[People] Where [years] <= @td

If this something you would be repeating a lot you can have it as a stored procedure

CREATE PROC proc_name (@dt datetime)
as    SELECT COUNT([years])+1 FROM [practice].[dbo].[People] Where [years] <= @td

and you would call it as such

exec proc_name ('20200801')

Upvotes: 1

Related Questions