how to calculate YTD average for each day in sql?-CodePudding

dates      product price

| 2020-01-01 | laptop | 210 |

| 2020-01-01 | pc | 100 |

| 2020-01-02 | laptop | 150 |

| 2020-01-02 | pc | 200 |

| 2020-01-03 | laptop | 150 |

| 2021-01-01 | pc | 110 |

| 2021-01-01 | laptop | 220 |

2021-01-02 | pc | 210 |

2021-01-02 | laptop | 200 |

2021-01-03 | pc | 260 |

it is necessary to calculate the average value since the beginning of the year for each day, by product For example. Add the date 2020.01.02 to 2020.01.01 and divide by two(150 210)/2

CodePudding user response：

Try something like:

SELECT product, AVG(price) FROM your_table WHERE dates >= '2021-01-01' GROUP BY product

SELECT dates, AVG(price) FROM your_table WHERE dates >= '2021-01-01' GROUP BY dates

SELECT product, dates, AVG(price) FROM your_table WHERE dates >= '2021-01-01' GROUP BY product, dates

I don't know mysql but in sql server and oracle database it would be something like that.

CodePudding user response：

WITH YOUR_TABLE_DATA (DATED,PRODUCT,PRICE)AS
(
  SELECT CAST('2020-01-01' AS DATE)DATED,'laptop',  210 UNION ALL 
  SELECT '2020-01-01',  'pc',  100 UNION ALL 
  SELECT'2020-01-02' , 'laptop',  150 UNION ALL 
  SELECT'2020-01-02',  'pc',  200 UNION ALL 
  SELECT'2020-01-03',  'laptop',  150 UNION ALL 
  SELECT '2021-01-01',  'pc' , 110 UNION ALL 
  SELECT'2021-01-01',  'laptop',  220 UNION ALL 
  SELECT'2021-01-02',  'pc',  210 UNION ALL 
  SELECT'2021-01-02',  'laptop',  200 UNION ALL 
  SELECT'2021-01-03',  'pc',  260  
)
SELECT C.DATED,C.PRODUCT,C.PRICE,
 AVG(C.PRICE)OVER(PARTITION BY C.PRODUCT ORDER BY C.DATED ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)X_AVG
FROM YOUR_TABLE_DATA AS C
ORDER BY C.PRODUCT,C.DATED

Tested on MySQL8.0 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b0124122a665975bf096ff02ea4832f0