CODEWITHSUNDEEP
sqloracle-database
rishi
rishi

Reputation: 3

SQL, select sum of values by group

I have a table product with the columns id, stock and rate.

I want a list of product with the sum of stock and the average rate.

The output in Oracle SQL needs to be as follows:

id      stock       rate
-------------------------
x        4            12.00
y        5            24.00
z        8             6.00
---------------------------
         17           14.00

Is it possible to write an SQL query that will give me the last row with 17 and 14?

Upvotes: 0

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can get the total with rollup. But there is a trick, because the data is not aggregated. So, aggregate by the id, which does nothing, but does allow the rollup to produce the total:

select id, sum(stock) as stock, avg(rate) as rate
from product
group by rollup(id);

Upvotes: 2

Related Questions