SQL perform AVG after MAX

I have two tables. Table1:

| ID1 | ID2 | ID3 | ID4 |
|-----+-----+-----+-----|
| 200 | 125 | 300 | 201 |
| 206 | 128 | 650 | 261 |
| 230 | 543 | 989 | 403 |

and Table2:

| ID1 | ID2 | ID3 | ID4 |  Date  | Cost |
|-----+-----+-----+-----+--------+------|
| 200 | 125 | 300 | 201 | 1/1/19 | 0.32 |
| 200 | 125 | 300 | 201 | 1/1/19 | 0.33 |
| 200 | 125 | 300 | 201 | 1/1/19 | 0.34 |
| 200 | 125 | 300 | 201 | 1/2/13 | 0.00 |
| 200 | 125 | 300 | 201 | 9/5/05 | 0.01 |

I am trying to Join Table1 to Table2 while filtering the outputs so only one row where date is at its maximum for those classifications are displayed and the average cost at that maximum date is also displayed. Here is my current code:

SELECT t1.ID1, t1.ID2, t1.ID3, t1.ID4, maxDate, avgCost
FROM Table1 t1
JOIN ( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2
ON t2.ID1 = t1.ID1
AND t2.ID2 = t1.ID2
AND t2.ID3 = t1.ID3
AND t2.ID4 = t1.ID4

With the example data given above, my result looks like this:

| ID1 | ID2 | ID3 | ID4 |  MaxDate  | AvgCost |
|-----+-----+-----+-----+-----------+---------|
| 200 | 125 | 300 | 201 |  1/1/19   |   0.20  |

When it should just be looking like this

| ID1 | ID2 | ID3 | ID4 | MaxDate   | AvgCost |
|-----+-----+-----+-----+-----------+---------|
| 200 | 125 | 300 | 201 |   1/1/19  |   0.33  |

The average cost is including values where date is not at its maximum. I’m assuming this is due to AVG(cost) being executed before Table2 is filtered with MAX(Date). Here’s what I’ve tried:

SELECT t1.ID1, t1.ID2, t1.ID3, t1.ID4, maxDate, avgCost
FROM Table1 t1
JOIN ( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2
ON t2.ID1 = t1.ID1
AND t2.ID2 = t1.ID2
AND t2.ID3 = t1.ID3
AND t2.ID4 = t1.ID4
WHERE maxDate = (SELECT MAX(Date) from Table2);

and

SELECT t1.ID1, t1.ID2, t1.ID3, t1.ID4, maxDate, avgCost
FROM Table1 t1
JOIN ( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2
ON t2.ID1 = t1.ID1
AND t2.ID2 = t1.ID2
AND t2.ID3 = t1.ID3
AND t2.ID4 = t1.ID4
WHERE maxDate = (SELECT MAX(Date) from Table2 GROUP BY ID1, ID2, ID3, ID4);

The first gives no results and the second results in an error, ORA-01427: single-row subquery returns more than one row. The rest of what I’ve tried is basically variations of the above, but I’m still not getting the results expected. I’m not sure how to make the AVG function perform only where Date is at its max…

Answer

Your definition of t2 looks like this:

( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2

Instead, to compute the average only over the most recent date, it should use a different aggregate function – the LAST function, like so:

( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, 
         AVG(Cost) KEEP (DENSE_RANK LAST ORDER BY Date) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2