Calculate average with result of Pivot

Question

Calculate average with result of Pivot

Navigation

#1 by (3 votes)

1

I have a query that returns me the student grades in certain tests. The amount of test can vary. I use the Pivot command to transform the test names into columns and thus 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 amount of test can vary.

My query looks 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

sql query pivot

asked by anonymous 29.06.2016 / 16:32

1 answer

Point to different directory in .htaccess Error trying to generate a signed APK

score 3 · Answer 1

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

Based on the main query, and assuming it is to do 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

There may still be a problem here (assuming that the [test_file] table has only the tests replied, not all the ones that can be answered): Do not the tests count for the average?

If they do not count. All ok.
If they are counted, the left join must be done with the table [make_test] and added a relationship with the table that has the available tests. So the line media = avg (ft.nota_num) should be changed to media = avg (isnull (ft.nota_num, 0)) , and count = 0.