CODEWITHSUNDEEP
postgresql
Robin Sage
Robin Sage

Reputation: 959

How to get SUM and AVG from a column in PostgreSQL

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:

enter image description here

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!

Upvotes: 0

Views: 828

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13019

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 ();

Upvotes: 1

clemens
clemens

Reputation: 17711

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.

Upvotes: 0

Related Questions