How to compute average Cost in SQL based on category?


So let’s say we have a table called phone.

and i need another column to show the average cost of a brand phone
I know i can do something like this:
SELECT brand, AVG (cost)
FROM Phone
GROUP BY brand;

and get something like this table:

Can someone help me how i would get this result below using a select sql statement?

>Solution :

You can use avg(cost) over (partition) such as this:

select *, 
 round(avg(cost) over (partition by brand), 2) as avg_cost
from phone;
phoneid brand cost avg_cost
2 apple 8 10.00
6 apple 12 10.00
3 google 7 6.50
4 google 6 6.50
1 samsung 10 9.33
5 samsung 4 9.33
7 samsung 14 9.33

Leave a ReplyCancel reply