CODEWITHSUNDEEP
sqlaggregate-functionscommon-table-expression
Stone Temple Aviators
Stone Temple Aviators

Reputation: 145

SQL Query Sum up Values Actual Year to Date and What the Average YTD

I need a query that, totals up expenses Actual YTD, Average YTD( out of all the years , what is my average year), Last Year YTD

I need 3 pieces of Data: Actual YTD | Average YTD | Last Years|

This is what I have so far:

select  SUM(Expenses)
        ,DATEADD(year, datediff(year,0,[Date]), 0) 
from    table1 
group by  
        DATEADD(year, datediff(year,0,[Date]), 0) 
)

Upvotes: 3

Views: 8367

Answers (2)

András Ottó
András Ottó

Reputation: 7695

Try this:

DECLARE @currentYear int
SELECT @currentYear = DATEPART(year, GETDATE())

SELECT SUM(CASE WHEN DATEPART(year,[date]) = @currentYear 
    THEN Expenses ELSE 0 END) AS 'Actual YTD',
SUM(Expenses) / COUNT( DISTINCT DATEPART(year,[date])) AS 'Average YTD',
SUM(CASE WHEN DATEPART(year,[date]) = @currentYear -1 
    THEN Expenses ELSE 0 END) AS 'Last Year YTD'

FROM Table1

SQL Fiddle

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Try this

select 

    AVG(case when 
        [date]>=DATEADD(year, datediff(year,0,getDate())-1, 0)  and 
        [date]<DATEADD(year, datediff(year,0,getDate()), 0) 
        then Expenses end) 
        as average_ytd,

    sum(case when 
        [date]>=DATEADD(year, datediff(year,0,getDate())-1, 0)  and 
        [date]<DATEADD(year, datediff(year,0,getDate()), 0) 
        then Expenses else 0 end) 
        as actual_ytd,

    sum(case when 
        [date]>=DATEADD(year, datediff(year,0,getDate())-2, 0)  and 
        [date]<DATEADD(year, datediff(year,0,getDate())-1, 0) 
        then Expenses else 0 end) 
        as lat_year_ytd
from
    table1

Upvotes: 1

Related Questions