3
The question asks me to show the number of students with averages greater than 7, less than 7 or equal to 7 for some tables I have here. Thus showing:
How to create this column of Description?
1 answer
7
AVG (Transact-SQL)
Returns the mean of the values in a group. The null values are ignored.
Since you gave no details, I assume your SQL will be something like:
SELECT CASE
WHEN AVG(NOTA) > 7 THEN 'Superior a 7'
WHEN AVG(NOTA) = 7 THEN 'Igual a 7'
ELSE ' Inferior a 7'
END 'Desrição',
COUNT(*) Quantidade
FROM ALUNO
GROUP BY CLASSE
-
1
Missed the
GROUP BYby class.
Browser other questions tagged sql database sql-server
You are not signed in. Login or sign up in order to post.
It’ll be something like:
SELECT CASE WHEN AVG(NOTA) > 7 THEN'Superior a 7' WHEN AVG(NOTA) = 7 THEN 'Igual a 7' ELSE ' Inferior a 7' END 'Desrição', COUNT(*)
FROM ALUNO– Marconi
You can do this using CASE
– Reginaldo Rigo
@Marconi, I don’t have create here, because the tables were already created when I went to solve the question (they are huge tables included). But to show the name of the class and the number of students with average above 7, I must make a Join between the cla_class tables (which contains the name of the class), ava_rating, apa_aproveitamento_student (which contains the grades of the students) and alu_student (which has the information of each student)
– M.Amaral
@M.Amaral I understood, my answer helps?
– Marconi
@Marconi Testo in a little while and I’ll get back to you
– M.Amaral
@M.Amaral edited his question to cover a little more, thus making it more accessible to future readers.
– Marconi
@M.Amaral gave it right?
– Marconi
it did work, and then also managing to do it using Union and doing the description column in select itself, simply doing something like
select 'Superior a 7' Descrição,– M.Amaral