how to list specific data entrie values greater than the average

Question

Hi i have to show the noetud and the note_final that are greater than the average note_final for nocours 420111TT during lasession A2019. How would I write down this query in mysql? thank you for the help I'm learning all this stuff for school and find it fun but challenging and this really has me stumped for now.

nocours	noetud	lasession	date_inscrite	note_final
320112TT	ALCE7888	H2019	2019-01-06	90
320112TT	GAPM9076	H2019	2019-01-06	65
320112TT	HEPP8945	H2019	2019-01-06	87
320112TT	PATE8756	H2019	2019-01-06	70
420111TT	ALCE7888	A2019	2019-09-08	78
420111TT	GAPM9076	A2019	2019-09-08	75
420111TT	HILA7890	A2019	2019-09-08	77
420111TT	PATE8756	A2019	2019-09-08	83
444678TT	ALCE7888	E2019	2019-06-21	85
444678TT	HILA7890	E2019	2019-06-21	80
444678TT	PATE8756	E2019	2019-06-21	90

Answer 1

If I follow you correctly, you want all rows for the given (nocrous, lasession) that are above the average

One option uses window functions:

select *
from (
    select t.*, avg(note_final) over() avg_note_final
    from mytable t
    where nocrous = '420111TT' and lasession = 'A2019'
) t
where note_final > avg_note_final

In versions 5.x of MSQL, you can use a subquery instead:

select t.*
from mytable t
where nocrous = '420111TT' and lasession = 'A2019' and note_final > (
    select avg(t1.note_final) 
    from mytable t1 
    where t1.nocrous = t.nocrous and t1.lasession = t.lasession
)