How do I create a new column in SQL that displays the total average for another field in each row?

Question

I'd like to figure out how I can insert a column average as a NEW field in SSMS. Let me use the following table as an example:

Style |   Car    | Sales
------|----------|-------
Sedan | Civic    | $100
Sedan | Accord   | $200
SUV   | CR-V     | $150
SUV   | Odyssey  | $150

The average of this table is $150 {AVG(Sales)}

What I want to know, is how can I create a new View that inserts this average as a new column? The view would looks something like this:

Style |   Car    | Sales | Avg Sales
------|----------|-------|-----------
Sedan | Civic    | $100  | $150
Sedan | Accord   | $200  | $150
SUV   | CR-V     | $150  | $150
SUV   | Odyssey  | $150  | $150

I've done some significant searching, as I thought this would be a simple operation. However, I'm only able to find information on how to group averages or how to display the average.

Answer 1

Use window functions:

create view myview as
select
    t.*,
    avg(sales) over(partition by style) avg_sales
from mytable t

If you want the average of the whole table, then:

create view myview as
select
    t.*,
    avg(sales) over() avg_sales
from mytable t