Aplicando matematicas:
Código SQL:
Ver originalCREATE TABLE #ALUMNO (Nombre VARCHAR(10), calificacion INT)
INSERT INTO #ALUMNO VALUES('xx', 9)
INSERT INTO #ALUMNO VALUES('yy', 10)
INSERT INTO #ALUMNO VALUES('zz', 5)
INSERT INTO #ALUMNO VALUES('aa', 6)
INSERT INTO #ALUMNO VALUES('bb', 7)
INSERT INTO #ALUMNO VALUES('qq', 10)
INSERT INTO #ALUMNO VALUES('oo', 5)
INSERT INTO #ALUMNO VALUES('cc', 8)
INSERT INTO #ALUMNO VALUES('dd', 6)
INSERT INTO #ALUMNO VALUES('ee', 5)
SELECT COUNT(Nombre) AS [Total Alumnos],
SUM (CASE WHEN calificacion > 5 THEN 1 ELSE 0 END) AS Aprobados,
CAST(SUM (CASE WHEN calificacion > 5 THEN 1 ELSE 0 END) AS DECIMAL(10,2)) / CAST(COUNT(Nombre) AS DECIMAL(10,2)) AS '%',
SUM (CASE WHEN calificacion <= 5 THEN 1 ELSE 0 END) AS [No aprobados],
CAST(SUM (CASE WHEN calificacion <= 5 THEN 1 ELSE 0 END) AS DECIMAL(10,2)) / CAST(COUNT(Nombre) AS DECIMAL(10,2)) AS '%'
FROM #alumno