Problem counting all records by specialty and adding record with total

Question

Problem counting all records by specialty and adding record with total

Navigation

#1 by (1 votes)

3

I need to extract the total of all records by specialty and, in turn, add this number of records.

This is my SQL query:

SELECT t.empresa,
       Count(t.idespecialidad) AS todos,
       e.nombre                AS nombre_especialidad
FROM   tickets AS t
       LEFT 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
       LEFT 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,
          t.empresa

Result I get now:

mysql sql

asked by Oswuell 11.05.2018 в 15:10

source

1 answer

Error adding the array with double data problem with a query prepared in php and mysqli

score 1 · Accepted Answer

The work can be done using GROUP BY in the main query (otherwise you will get the total):

SELECT t.empresa,
       COUNT(t.idespecialidad) AS todos,
       e.nombre                AS nombre_especialidad
FROM   tickets AS t
       LEFT 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,
          t.empresa

In the second query, a COUNT(*) without a GROUP BY associated to count all the records obtained by the WHERE :

SELECT t.empresa,
       COUNT(*) todos,
       'Total' nombre_especialidad
FROM   tickets AS t
       LEFT 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'

The full SQL query would be:

SELECT t.empresa,
       COUNT(t.idespecialidad) AS todos,
       e.nombre                AS nombre_especialidad
FROM   tickets AS t
       LEFT 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,
          t.empresa
UNION ALL
SELECT t.empresa,
       COUNT(*) todos,
       'Total' nombre_especialidad
FROM   tickets AS t
       LEFT 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'

You can see a online example here .