Get the number of rows of an SQL query

Question

Get the number of rows of an SQL query

Navigation

#1 by (1 votes)

1

I have a problem in a query, in this query I have the students enrolled in a section of a specific year and their averages. My goal is how I can get the number of students who pulled at least one course in that year.

select N.Descripcion as Nivel,G.Grado, S.Nombre as Seccion,A.Nombre,
M.MatriculaID
 ,AA.Denominacion as Periodo  , Promedio=AVG(E.Nota), c.Nombre as Curso  
from alumno A 
inner join Matricula M
ON A.AlumnoID=M.FK_AlumnoID
inner join AÑO_ACADEMICO aa
ON aa.AñoID=M.FK_AñoID
inner join SECCION S
ON S.SeccionID=M.FK_SeccionID
inner join Nivel N
ON N.NivelID=S.FK_NivelID
inner join Grado g
ON G.GradoID=S.FK_GradoID
inner join Evaluacion E
ON M.MatriculaID= E.FK_MatriculaID 
inner join Bimestre B
ON B.BimestreID=E.FK_BimestreID AND B.FK_AñoID=AA.AñoID
inner join detalle_curso DC
ON DC.DetalleCursoID=E.FK_DetalleCursoID
inner join curso c
ON C.CursoID=DC.FK_CursoID
 Group by N.Descripcion,G.Grado,S.Nombre,A.Nombre,M.MatriculaID,AA.Denominacion ,C.Nombre 
 having AVG(E.Nota)<10.5
 order by AA.Denominacion,N.Descripcion,G.Grado,S.Nombre,A.Nombre asc

Another problem is that if the student pulled two courses in a year, I want to count it as one. Currently the result of my query is this.

sql consulta-sql consulta subconsultas

asked by Luis Fernando Zumaran 18.10.2018 в 01:22

source

1 answer

Show alert with a Gauge chart Problem with java threads

score 1 · Accepted Answer

To have students tell you how you can use SQL SELECT DISTINCT Statement . The code would look like this:

select DISTINCT M.MatriculaID, N.Descripcion as Nivel,G.Grado, S.Nombre as 
Seccion,A.Nombre, AA.Denominacion as Periodo  , Promedio=AVG(E.Nota), c.Nombre as 
Curso  
from alumno A 
inner join Matricula M
ON A.AlumnoID=M.FK_AlumnoID
inner join AÑO_ACADEMICO aa
ON aa.AñoID=M.FK_AñoID
inner join SECCION S
ON S.SeccionID=M.FK_SeccionID
inner join Nivel N
ON N.NivelID=S.FK_NivelID
inner join Grado g
ON G.GradoID=S.FK_GradoID
inner join Evaluacion E
ON M.MatriculaID= E.FK_MatriculaID 
inner join Bimestre B
ON B.BimestreID=E.FK_BimestreID AND B.FK_AñoID=AA.AñoID
inner join detalle_curso DC
ON DC.DetalleCursoID=E.FK_DetalleCursoID
inner join curso c
ON C.CursoID=DC.FK_CursoID
 Group by N.Descripcion,G.Grado,S.Nombre,A.Nombre,M.MatriculaID,AA.Denominacion ,C.Nombre 
 having AVG(E.Nota)<10.5
 order by AA.Denominacion,N.Descripcion,G.Grado,S.Nombre,A.Nombre asc

To count the number of students who took at least one course. You could use SQL COUNT () . The code would look like this:

select COUNT(DISTINCT M.MatriculaID) 
from alumno A 
inner join Matricula M
ON A.AlumnoID=M.FK_AlumnoID
inner join AÑO_ACADEMICO aa
ON aa.AñoID=M.FK_AñoID
inner join SECCION S
ON S.SeccionID=M.FK_SeccionID
inner join Nivel N
ON N.NivelID=S.FK_NivelID
inner join Grado g
ON G.GradoID=S.FK_GradoID
inner join Evaluacion E
ON M.MatriculaID= E.FK_MatriculaID 
inner join Bimestre B
ON B.BimestreID=E.FK_BimestreID AND B.FK_AñoID=AA.AñoID
inner join detalle_curso DC
ON DC.DetalleCursoID=E.FK_DetalleCursoID
inner join curso c
ON C.CursoID=DC.FK_CursoID
 Group by N.Descripcion,G.Grado,S.Nombre,A.Nombre,M.MatriculaID,AA.Denominacion ,C.Nombre 
 having AVG(E.Nota)<10.5
 order by AA.Denominacion,N.Descripcion,G.Grado,S.Nombre,A.Nombre asc

I hope it serves you. Greetings!