MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


create table matchresult
( ath_id  integer
, thedate  date
, earnings  integer )  
insert into matchresult values
 ( 1 , '2024-01-01' , 100 )
,( 1 , '2024-01-02' , 200 )
,( 1 , '2024-01-03' , 300 )
,( 1 , '2024-01-04' , 400 )
,( 2 , '2024-02-01' , 10 )
,( 3 , '2024-03-01' , 3000 )
,( 3 , '2024-03-02' , 5000 )
;

Records: 7  Duplicates: 0  Warnings: 0


SELECT matchresult.ath_id
     , SUM(matchresult.earnings) AS SUMEARN 
     , AVG(matchresult.earnings) AS AVGEARN 
  from matchresult
GROUP BY matchresult.ath_id


ath_id
SUMEARN
AVGEARN


1
1000
250.0000

2
10
10.0000

3
8000
4000.0000


SELECT matchresult.ath_id
     , SUM(matchresult.earnings) AS SUMEARN 
  --   , AVG(matchresult.earnings) AS AVGEARN 
  from matchresult
GROUP BY matchresult.ath_id
HAVING SUMEARN < AVG(SUMEARN)

Invalid use of group function