# get sum and average of values by date

• ### Question

• I have following table

StartDate          EndDate         Coins

2017/01/01   2017/01/01          10

2017/01/05   2017/01/05          10

2017/02/01   2017/02/01           5

2017/02/04   2017/03/04           20

How can i calculate sum and average of the coins

Month         Sum    avg

2017/01       20      0.64  (20(total coins) / (total days)31)

2017/02       25      0.89 ( 25/28 )

Wednesday, June 14, 2017 8:35 PM

• ```CREATE TABLE mytable(
StartDate DATE  NOT NULL PRIMARY KEY
,EndDate   DATE  NOT NULL
,Coins     INTEGER  NOT NULL
);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/01/01','2017/01/01',10);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/01/05','2017/01/05',10);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/02/01','2017/02/01',5);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/02/04','2017/03/04',20);

select convert(varchar(7),StartDate,111) [Month] , convert(varchar(7),min(StartDate),111)
, sum(Coins) as [Sum] ,
sum(Coins)*1.0 /(datediff(day,min(startDate),eomonth(min(startDate)))+1) [avg]

from mytable
Group by  convert(varchar(7),StartDate,111)

drop table mytable```

• Proposed as answer by Wednesday, June 14, 2017 11:15 PM
• Marked as answer by Friday, June 16, 2017 3:39 PM
Wednesday, June 14, 2017 8:49 PM

### All replies

• ```CREATE TABLE mytable(
StartDate DATE  NOT NULL PRIMARY KEY
,EndDate   DATE  NOT NULL
,Coins     INTEGER  NOT NULL
);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/01/01','2017/01/01',10);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/01/05','2017/01/05',10);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/02/01','2017/02/01',5);
INSERT INTO mytable(StartDate,EndDate,Coins) VALUES ('2017/02/04','2017/03/04',20);

select convert(varchar(7),StartDate,111) [Month] , convert(varchar(7),min(StartDate),111)
, sum(Coins) as [Sum] ,
sum(Coins)*1.0 /(datediff(day,min(startDate),eomonth(min(startDate)))+1) [avg]

from mytable
Group by  convert(varchar(7),StartDate,111)

drop table mytable```

• Proposed as answer by Wednesday, June 14, 2017 11:15 PM
• Marked as answer by Friday, June 16, 2017 3:39 PM
Wednesday, June 14, 2017 8:49 PM
• Hi SqlRockss,

If the version of your SQL Server is below SQL Server 2012, you could also use MONTH,YEAR,DATEADD,DATEDIFF function to get results. Here is the example code for your reference.

```create table TableTest
(
StartDate DATE  NOT NULL PRIMARY KEY,
EndDate   DATE  NOT NULL,
Coins     INTEGER  NOT NULL
)

INSERT INTO TableTest(StartDate,EndDate,Coins) VALUES ('2017/01/01','2017/01/01',10);
INSERT INTO TableTest(StartDate,EndDate,Coins) VALUES ('2017/01/05','2017/01/05',10);
INSERT INTO TableTest(StartDate,EndDate,Coins) VALUES ('2017/02/01','2017/02/01',5);
INSERT INTO TableTest(StartDate,EndDate,Coins) VALUES ('2017/02/04','2017/03/04',20);
Go

select cast (YEAR(StartDate) as varchar(4))+'/'+right('0'+cast(MONTH(StartDate) as varchar(2)),2) as [Month],
sum(Coins) as [Sum],
cast( round(sum(Coins)*1.0/day(dateadd(mm,datediff(mm,0,max(StartDate))+1,0)-1),2,1) as numeric(8,2))  as [avg] from TableTest
group by YEAR(StartDate),MONTH(StartDate)```

Best Regards,

Will

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, June 15, 2017 5:09 AM
• Hi Try This

DECLARE  @Table TABLE (StartDate dateNotnull,ENDDate date,Coins int)
INSERTINTO@Table
SELECT'2017/01/01','2017/01/01',10
UNIONALL
SELECT'2017/01/05','2017/01/05',10
UNIONALL
SELECT'2017/02/01','2017/02/01',5
UNIONALL
SELECT'2017/02/04','2017/02/04',20
--SELECT * FROM @Table
SELECT
FROM(
SELECT
ROW_NUMBER()OVER(PARTITIONBYMonth(StartDate),Year(StartDate)ORDERBYMonth(StartDate),Year(StartDate))ASRownum,
StartDate,SUM(Coins)OVER(PartitionBYMonth(StartDate),Year(StartDate))ASCoinSum
FROM@Table)ASD WHERED.Rownum =1

Dilip

Thursday, June 15, 2017 6:52 AM
• ```create table test(StartDate date, EndDate date, Coins int)

insert into test values('2017/01/01', '2017/01/01', 10)
insert into test values('2017/01/05','2017/01/05',10)
insert into test values('2017/02/01','2017/02/01',5)
insert into test values('2017/02/04','2017/03/04',20)

;with cte as(
select (cast(datepart(year,StartDate) as varchar) + '/' + cast(datepart(month,StartDate) as varchar)) as date,eomonth(StartDate) as lastday, sum(Coins) as Coins from test group by StartDate
)
select date, sum(Coins), sum(Coins), datepart(day,lastday), cast(sum(Coins)*1.0/datepart(day,lastday) as decimal(10,2)) from cte group by date,lastday
```
Thursday, June 15, 2017 9:41 AM