oracle - How to subtract mean between groups in SQL?

Question

I have a table like:

AVG_AMOUNT	WAS_MEMBER	YEAR
200.00	True	2018
100.00	False	2018
20.00	True	2019
300.00	False	2019
400.00	True	2020
10.00	False	2020

question from:https://stackoverflow.com/questions/65648966/how-to-subtract-mean-between-groups-in-sql

Answer 1

You can use conditional aggregation containing SUM() such as

SELECT SUM(CASE WHEN was_member='True'  THEN   avg_amount 
                WHEN was_member='False' THEN - avg_amount END) AS diff_avg_amount, 
       year
  FROM tab
 GROUP BY year
 ORDER BY year