CODEWITHSUNDEEP
sql-server
Christian Aldana
Christian Aldana

Reputation: 77

Get the sum of the column

Get the sum of the Patient column and sort in DESC order without taking into account the SUM row

The query is the following:

SELECT 
    C.Especialidad, COUNT(P.ClaveConsultorio) AS Paciente
FROM 
    CONSULTORIOS C 
INNER JOIN
    pacientes P ON C.ClaveConsultorio = P.ClaveConsultorio
GROUP BY
    C.Especialidad 
 
UNION

SELECT
    'Total', COUNT(P.ClaveConsultorio) AS Paciente
FROM  
    CONSULTORIOS C 
INNER JOIN
    pacientes P ON C.ClaveConsultorio = P.ClaveConsultorio
ORDER BY
    2 DESC

enter image description here

Upvotes: 1

Views: 61

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

We can try a group by with rollup here:

SELECT COALESCE(c.Especialidad, 'Total') AS Especialidad,
       COUNT(p.ClaveConsultorio) AS Paciente
FROM CONSULTORIOS c
INNER JOIN pacientes p ON c.ClaveConsultorio = p.ClaveConsultorio
GROUP BY ROLLUP(c.Especialidad);

Upvotes: 3

Related Questions