How to find the average of a distinct value based on a count in adjacent column

Question

I'm trying to find the average "DAYS_FROM_OPN_2_CALL" based on the "TOTAL_CALLS" column.

I'm assuming theirs a fairly simple way to do this that I'm overlooking

My data set looks like this ;

Header 1 = days to open (eg. 1,2,3,4,5) - Header2= total calls (eg., 523,469,1020,234,675)

Answer 1

I'm trying to find the average "DAYS_FROM_OPN_2_CALL" based on the "TOTAL_CALLS" column.

GROUP BY the total_calls and then use the AVG aggregation function to get the average DAYS_FROM_OPN_2_CALL:

SELECT total_calls,
       AVG(DAYS_FROM_OPN_2_CALL) AS avg_days_from_opn_2_call
FROM   table_name
GROUP BY total_calls;

Which, for the sample data:

CREATE TABLE table_name (days_from_opn_2_call, total_calls) AS
SELECT -115, 2 FROM DUAL UNION ALL
SELECT -111, 1 FROM DUAL UNION ALL
SELECT -110, 5 FROM DUAL UNION ALL
SELECT -109, 9 FROM DUAL UNION ALL
SELECT -108, 1 FROM DUAL UNION ALL
SELECT -107, 3 FROM DUAL;

Outputs:

TOTAL_CALLS	AVG_DAYS_FROM_OPN_2_CALL
2	-115
1	-109.5
5	-110
9	-109
3	-107

db<>fiddle here