none
get sum and average of values by date RRS feed

  • 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

Answers

  • 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)
    
    --Please don't use reserved words for your objects names.
    
     
     
    
    drop table mytable

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

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)
    
    --Please don't use reserved words for your objects names.
    
     
     
    
    drop table mytable

    • Proposed as answer by DeviantLogic Wednesday, June 14, 2017 11:15 PM
    • Marked as answer by SqlRockss Friday, June 16, 2017 3:39 PM
    Wednesday, June 14, 2017 8:49 PM
    Moderator
  • 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
    Moderator
  • 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
    StartDate,CoinSum,CAST(CoinSum/CAST(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,StartDate),0)))ASNUMERIC(10,2))ASNUMERIC(4,2))AsAverage
    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