Search code examples
sqlpostgresqlpostgresql-9.5

Postgres - AVG calculation

Please refer to the below query

SELECT  sum(sales) AS "Sales",
        sum(discount) AS "discount",
        year
 FROM Sales_tbl 
 WHERE Group by year

Now I want to also display a column for AVG(sales) that is the same value and based on the total of sales column

Please refer to the below, I need to derive the 4th column

Output

Please advise

Solution

  • Use AVG() as a window function:

    WITH t AS (
  SELECT 
    SUM(sales) AS sales, SUM(discount) AS discount, year
  FROM tbl_sales
  GROUP BY year 
) 
SELECT *,AVG(sales) OVER w_total 
FROM t
WINDOW w_total AS (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY year;

    The frame RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is pretty much optional in this case, but it is considered a good practice to be as explicit as possible in window functions. So you're also able to write the query like this:

    WITH t AS (
  SELECT 
    SUM(sales) AS sales, SUM(discount) AS discount, year
  FROM tbl_sales
  GROUP BY year
) 
SELECT *,AVG(sales) OVER () 
FROM t
ORDER BY year;

    Demo: db<>fiddle