CODEWITHSUNDEEP
sql
nbs335
nbs335

Reputation: 9

SQL : how to retrieve the mean of the previous year?

I have an issue with my dataset

Month Year Sales
1 2021 100
2 2021 150
3 2021 200
1 2022 100
2 2022 140
3 2022 120
1 2023 100
2 2023 100
3 2023 100

I want to add a new column with, for each row, the sales average of the previous year to obtain this :

Month Year Sales Avg Sales Previous year
1 2021 100 null
2 2021 150 null
3 2021 200 null
1 2022 100 150
2 2022 140 150
3 2022 120 150
1 2023 100 120
2 2023 100 120
3 2023 100 120

I tried this :

lag("Sales",1)OVER ( PARTITION BY "month" ORDER BY "year", "month" ASC )

But it don't work for each rows

Do yo have a solution ?

Thanks

Upvotes: 1

Views: 78

Answers (3)

jnisen
jnisen

Reputation: 64

I have a solution that can solve your question.

SELECT 
    s.month, 
    s.year, 
    s.sales, 
    sales_year.avg_sales as avg_sales_prev_year
FROM salesdata s
LEFT JOIN 
    (SELECT 
        year, 
        round(avg(sales),0) as avg_sales
    from salesdata
GROUP BY year) as sales_year 
on sales_year.year = s.year - 1

Upvotes: 0

A.Copter
A.Copter

Reputation: 360

The same approach as am2 just for an update statement as you ask how to populate a column with the average value.

UPDATE data
SET year_mean = subquery.mean
FROM (
  SELECT year, avg(sales) as "mean" FROM data
  GROUP BY year
) as subquery
WHERE data.year = (subquery.year - 1);

The exact syntax may vary depending on your DBMS but it should be possible to adapt this to every DBMS.

Example with postgres: DB<>Fiddle.uk

Upvotes: 0

am2
am2

Reputation: 371

I Do not know, whether there is a tricky way with windows functions to bind it on the average of the previos year. But you can easily do this that way (inline views):

WITH AvgYear AS (
    SELECT YEAR, AVG(SALES) AVG_SALES  
    FROM XY
    GROUP BY YEAR)
SELECT 
    XY.MONTH, XY.YEAR, XY.SALES, 
    AvgYear.AVG_SALES AVG_SALES_LAST_YEAR,
    AVG(XY.SALES) OVER (PARTITION BY XY.YEAR) AVG_SALES_THIS_YEAR   
FROM XY LEFT JOIN AvgYear ON (XY.YEAR-1) = AvgYear.YEAR
ORDER BY XY.YEAR, XY.MONTH

The average Sale for this year is no problem with windows- functions, as you can see.

Upvotes: 0

Related Questions