Pulling data from one or another column for an average, based on NULL of one column

I've searched for a while on how to do this but I've barely been using SQL Dev a week so I might be missing something obvious.

我已经搜索了一段时间如何做到这一点,但我几乎没有使用SQL Dev一周,所以我可能会遗漏一些明显的东西。

I'm trying to query an average value based on two columns. I can get averages for each column individually but I'm having trouble going further. Here's the table format:

我正在尝试根据两列查询平均值。我可以单独获得每列的平均值,但我很难进一步。这是表格格式:

Value  Override  ID  Hourend
19     (Null)    1   6:00
23     (Null)    1   4:00
45     18        1   6:00
66     (Null)    2   6:00
68     (Null)    2   5:00
72     (Null)    1   5:00
81     45        1   5:00

What I'm wanting is an average of the Value column per hour, by ID. That I've done using:

我想要的是ID每小时的Value列的平均值。我已经完成了使用:

SELECT AVG (Value)
  (OVER (PARTITION BY ID, Hourend) as Average
FROM Database.Table;

However, I also need to have an average with Override substituting Value of the same row when not Null. So basically I need a way to pull Override instead of Value when Override isn't Null.

但是,我还需要有一个平均值,当不是Null时,Override会替换同一行的Value。所以基本上我需要一种方法来在Override不是Null时拉取Override而不是Value。

I've fiddled with CASE statements and other tools with no success as of yet. Efficiency isn't vital at this point, I just need to make it work. Any help is greatly appreciated!

我已经摆弄了CASE语句和其他工具,但迄今为止还没有成功。效率在这一点上并不重要,我只需要让它发挥作用。任何帮助是极大的赞赏!

3 个解决方案

#1

You can do that with an inline IIF:

您可以使用内联IIF执行此操作:

SELECT AVG (IIF(Override IS NOT NULL, Override, Value))
    OVER (PARTITION BY ID, Hourend) as Average
FROM Database.Table;

IIF is available for SQL Server 2012 and up.

IIF适用于SQL Server 2012及更高版本。

#2

I would think this should work:

我认为这应该有效:

SELECT AVG (CASE WHEN Override IS NOT NULL THEN Override ELSE Value END)
OVER (PARTITION BY ID, Hourend) as Average
FROM Database.Table;

#3

-1

CREATE TABLE #Temp (Value int, Override int , ID int, Hourend varchar(10) )


INSERT INTO #Temp VALUES (19,Null,1,'6:00')
INSERT INTO #Temp VALUES(23,Null,1,'4:00')
INSERT INTO #Temp VALUES(45,18 ,1,'6:00')
INSERT INTO #Temp VALUES(66,Null,2,'6:00')
INSERT INTO #Temp VALUES (68,Null,2,'5:00')
INSERT INTO #Temp VALUES(72,Null,1,'5:00')
INSERT INTO #Temp VALUES(81,45 ,1,'5:00')

SELECT  ID,Hourend  ,AVG(COALESCE(CAST(Override as decimal) ,CAST(VAlue as decimal)))
FROM #Temp
GROUP BY ID,Hourend

This should do the trick, COALESCE selects Override if it's not null,else it will select Value.

这应该是技巧,如果它不是null,COALESCE选择Override,否则它将选择Value。

#1