Postgres - AVG calculation-CodePudding

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

Output

Please advise

CodePudding user response：

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

CodePudding user response：

Select from your query as a derived table, i.e. a sub-query. I have removed one WHERE from it.

select *, avg("Sales") as average
from -- your query as a derived table
(
 SELECT sum(sales) AS "Sales", sum(discount) AS "discount", "year" 
 FROM Sales_tbl 
 Group by "year"
) t;