How to get SUM and AVG from a column in PostgreSQL-CodePudding

Maybe I'm overlooking something, but none of the answers I found solve my problem. I'm trying to get the sum and average from a column, but everything I see is getting sum and average from a row.

Here is the query I'm using:

SELECT product_name,unit_cost,units,total,SUM(total),AVG(total)
FROM products
GROUP BY product_name,unit_cost,total

And this is what I get:

It returns the exact same amounts. What I need is to add all values in the unit_cost column and return the SUM and AVG of all its values. What am I missing? What did I not understand? Thank you for taking the time to answer!

CodePudding user response：

The groups in your query contain just one row if total is a distinct value. This seems to be the case with your example. You can check this with a count aggregate (value = 1).

Removing total and probably unit_cost) from your select and group by clause should help.

CodePudding user response：

AVG and SUM as window functions and no grouping will do the job.

select product_name,unit_cost,units,total,
       SUM(total) over all_rows as sum_of_all_rows,
       AVG(total) over all_rows as avg_of_all_rows
from products
window all_rows as ();