CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user3314399
user3314399

Reputation: 325

SQL Average By Column Name

I have the following T-SQL and having struggle trying to find out the average from beginning of the year (January) to the current month. For example, it should show the average from January to August as a column. I don't know how to get the AVG from column names until a specific month (current month):

select
    [Customer], category,
    [1] AS January, [2] AS Febrary, [3] AS March,
    [4] AS April, [5] AS May, [6] AS June,
    [7] AS July, [8] AS August, [9] AS September,
    [10] AS October, [11] AS November, [12] AS December 
from
    (select
         month(received) as my_month, [Customer], category 
     from
         Data 
     where 
         year(Received) = '2017') AS t
pivot 
    (count(my_month) for my_month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12])) as p

Upvotes: 0

Views: 1003

Answers (2)

Lassi Uosukainen
Lassi Uosukainen

Reputation: 1688

To me, it looks like you're approaching this from a completely wrong standpoint. Something like this should return an average of the current year until the beginning of the current month grouped by customer and category:

SELECT AVG(received), Customer, Category
  FROM Data
  WHERE YEAR(Received) = 2017 
    AND MONTH(Received) < MONTH(GETDATE())
  GROUP BY Customer, Category

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Just use conditional aggregation:

select customer, category,
       sum(case when month(received) = 1 then 1 else 0 end) as jan,
       sum(case when month(received) = 2 then 1 else 0 end) as feb,
       . . .,
       sum(case when month(received) <= 1 then 1 else 0 end) as thru_jan,
       sum(case when month(received) <= 2 then 1 else 0 end) as thru_feb,
       . . .
       sum(case when month(received) >= 1 and month(received) <= month(getdate()) then 1 else 0 end) as uptonow
from data
where received >= '2017-01-01' and received < '2018-01-01'
group by customer, category;

Upvotes: 2

Related Questions