I am needing help returning the current values in my SQL view in SQL Server.

I have a whole lot of columns that are joined by a UNION ALL in a SQL view. One of those values is the average pay of that individual over a year.

The current view looks something like:

(Please excuse the dummy variables and data)

This average value was calculated from two values in a different table, and has been joined onto the view with a LEFT OUTER JOIN. This means any future calculations based on [Average] will be outside the cte.

I am now wanting to add another column that obtains the 'Package Average'. This is the average of the average, but only accounting for each value once. In this example, that would be 45,000 (as opposed to 42,000 if we counted for the second 30,000).

I am also wanting this Package Average placed on every line of the view.

I know of AVG(DISTINCT [Average]) however that requires an aggregate. I also know of the analytical function avg([Average]) however that doesn't working with DISTINCT.

Any advice would be greatly appreciated.

CodePudding user response：

You could join to a sub-query that calculates that packaged average.

For example:

create view test_view as
select *
from (values
('A',     'X',    30000), 
('B',     'Y',    40000), 
('C',     'X',    50000), 
('D',     'Z',    30000), 
('E',     'Y',    60000)
) v(Person, Location, Average)

select v.*, q.*
from test_view v
cross join (
  select avg(distinct average) as PackageAverage
  from test_view
) q

Person | Location | Average | PackageAverage
:----- | :------- | ------: | -------------:
A      | X        |   30000 |          45000
B      | Y        |   40000 |          45000
C      | X        |   50000 |          45000
D      | Z        |   30000 |          45000
E      | Y        |   60000 |          45000

db<>fiddle here

CodePudding user response：

Unfortunately, as you say, there is no DISTINCT for window functions. But you can hack it with ROW_NUMBER:

• Calculate the row-number per distinct value.
• Conditionally aggregate only those values that have a row-number of 1

WITH YourView AS (
    SELECT *
    FROM (VALUES
        ('A',     'X',    30000), 
        ('B',     'Y',    40000), 
        ('C',     'X',    50000), 
        ('D',     'Z',    30000), 
        ('E',     'Y',    60000)
    ) v(Person, Location, Average)
),
Ranked AS (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY v.Average ORDER BY Person) AS rn
    FROM YourView v
)
SELECT *,
  AVG(CASE WHEN r.rn = 1 THEN r.Average END) OVER ()
FROM Ranked r;

db<>fiddle