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:
Window functions without grouping will help.
SELECT product_name,unit_cost,units,total,
SUM(total) over () sum_of_all,
AVG(total) over () avg_of_all
FROM products;