Calculate average with Pivot result

Question

I have a query that returns the grades of students in certain tests. The amount of test may vary. I use the Pivot command to transform test names into columns and so organize test/note, as shown in the following image:

Where "SENEM_01", "02", "03" are test names. What I need is to average these values. Remembering that the test amount may vary.

My appointment is like this:

WITH  notas_testes as(
    SELECT 
    ft.inscricao,
    ft.nota_num,
    ft.idteste,
    a.nome,
    ft.unidade
    from [Easy395].[dbo].[aluno] a
    INNER JOIN [Easy395].[dbo].[faz_teste] ft
        ON a.inscricao = ft.inscricao   
)

select *
from notas_testes
PIVOT (SUM(nota_num) FOR idteste IN ([SENEM_01], [SENEM_02], [SENEM_03]))pvt

Answer 1

The pivot is very useful to show the grade of all tests in only one row, however if the goal is to have a similar result, but replacing the 3 columns of test notes with only 1 with the final average, the best is to calculate the value without using the pivot.

Based on the main query, and assuming it is to make a simple arithmetic mean, it is:

SELECT  ft.inscricao,
        media = avg(ft.nota_num),
        --ft.idteste,
        a.nome,
        ft.unidade
from [Easy395].[dbo].[aluno] a
    INNER JOIN [Easy395].[dbo].[faz_teste] ft
        ON a.inscricao = ft.inscricao 
group by ft.inscricao, a.nome, ft.unidade

Here there may still be a problem (assuming that the [faz_teste] table has only the tests answered and not all that can be answered): Tests without grades count towards the average?

• If they don’t tell. It’s okay.
• If they count, the left Join should be done with the [faz_teste] table and added a relationship with the table that has the tests available. Thus, the line media = avg(ft.nota_num) should be amended to media = avg(isnull(ft.nota_num,0)), and will count with value=0.