Total count add sql [duplicate]

Question

Total count add sql [duplicate]

Navigation

#1 by (0 votes)

0

I want to show the total registration and at the same time add that total

SELECT 
t.empresa, 
COUNT(t.idespecialidad) AS count, 
e.nombre AS nombre_especialidad

    FROM tickets as t

        INNER JOIN especialidad AS e ON t.idespecialidad = e.idespecialidad

    WHERE t.empresa = '3' AND t.status= '4' AND t.cierre_ticket between '2018-05-01' AND '2018-05-31'

UNION ALL

    SELECT 'SUM', COUNT(t.idespecialidad), e.nombre AS nombre_especialidad

        FROM tickets as t

            INNER JOIN especialidad AS e ON t.idespecialidad = e.idespecialidad

        WHERE t.empresa = '3' AND t.status= '4' AND t.cierre_ticket between '2018-05-01' AND '2018-05-31'

 GROUP BY e.nombre

This is what I get:

empresa count   nombre_especialidad     
3       9       Mantención Central Telefónica
SUM     1       Aire Acondicionado
SUM     2       Electricidad
SUM     4       Mantención Central Telefónica
SUM     1       Revisión Enlace
SUM     1       Traslado Mobiliario

mysql sql mysqli

asked by Oswuell 10.05.2018 в 21:49

source

1 answer

Problem with chatbot "dialogflow" in Unity What does the symbol '~' mean in Bash?

score 0 · Answer 1

I understand that this is what you are looking for:

SELECT  t.empresa, 
        COUNT(t.idespecialidad) AS count, 
        e.nombre                AS nombre_especialidad
        FROM tickets as t
        INNER JOIN especialidad AS e 
            ON t.idespecialidad = e.idespecialidad
        WHERE   t.empresa = '3' 
            AND t.status= '4' 
            AND t.cierre_ticket between '2018-05-01' AND '2018-05-31'
        GROUP BY t.empresa, e.nombre    

UNION ALL

SELECT  'SUM', 
        COUNT(t.idespecialidad), 
        NULL
        FROM tickets as t
        INNER JOIN especialidad AS e 
            ON t.idespecialidad = e.idespecialidad
        WHERE   t.empresa = '3' 
                AND t.status= '4' 
                AND t.cierre_ticket between '2018-05-01' AND '2018-05-31'
        ORDER BY 1

Comments:

The first query is why you get the amount of idespecialidad by t.empresa and e.nombre . Note that both must be indicated in the GROUP BY , MySql is permissive in this, but I recommend as a good practice to always indicate all the columns that you show in SELECT that do not have an aggregation function.
The second query, is basically the first but without any GROUP BY , so the COUNT will count all the rows.
Check the ORDER BY , if you want the total as the last row, you must make sure that 'SUM 'is alphabetically after any t.empresa