MySQL - Conditional average in grouped data

Navigation
2

I am new in this field and I fall short in trying to solve what I want. In a table imported from excel I have the columns <grado, docente, curso, Nota1, Nota2, Nota3, Nota4, Nota5> some fields belonging to the notes contain an "X", my concern goes this way, I would like to generate a query similar to what results from the following: 

SELECT grado, docente, curso, AVG(Nota1), AVG(Nota2), AVG(Nota3), AVG(Nota4), AVG(Nota5) FROM tabla
GROUP BY docente,curso

However, the average I need in each of the notes should not consider the fields that contain "X", my first option was to add to the previous query a WHERE Nota1 <> "X" and so with all the notes but this also affects the average of the columns that do not have any "X" because it restricts the entire row.

How could I solve this problem? Thanks in advance.

    
asked by Gonzo 15.08.2017 в 23:32
source

2 answers

1

You should remove the condition from the where and make an IF or NULLIF in each column. When returning NULL in case the field is X, AVG will not average it. 

SELECT AVG(NULLIF(Nota1,'X')) FROM tabla
    
answered by 16.08.2017 / 00:22
source
1

You could solve this by using the case statement: 

SELECT grado, docente, curso,
AVG(CASE nota1 WHEN 'X' THEN NULL ELSE nota1 END) as 'Nota 1',
AVG(CASE nota2 WHEN 'X' THEN NULL ELSE nota2 END) as 'Nota 2',
AVG(CASE nota3 WHEN 'X' THEN NULL ELSE nota3 END) as 'Nota 3',
AVG(CASE nota4 WHEN 'X' THEN NULL ELSE nota4 END) as 'Nota 4',
AVG(CASE nota5 WHEN 'X' THEN NULL ELSE nota5 END) as 'Nota 5'
FROM docs;

I've attached the example: Example in Sql Fiddle

    
answered by 16.08.2017 в 01:27
Create a track (track) from a local branch to a remote branch How can I read the contents of a PDF and export it to .txt?