CODEWITHSUNDEEP
mysqlsql
lozadakb
lozadakb

Reputation: 304

MYSQL: Make multiple counts from one select

I want to make my report faster because is taking to long to load, i was wondering if there is a way to make multiple counts from just one select.

Example, make this select wish is not going to change for the counts

SELECT IDS, Fecha_Recarga, Banco_Recarga, Tipo, Status, RefVerif, MontoVerif, RecargaDuplicada FROM transaccionesrr WHERE Fecha_Recarga = '2017-02-07' AND Banco_Recarga = 'BANESCO' AND Tipo = 'RECARGA' AND Status = 'PROCESADA'

And based on the result of this select i want to make multiple counts like

Count (*) WHERE RefVerif = '1' AS RefVerif
Count (*) WHERE RefVerif = '1' AND MontoVerif = '0' AS MontoVerif 
Count (*) WHERE RecargaDuplicada = '1' AS Duplicada

This is the Stored Procedure that i have right know

SELECT COUNT(*) AS RECARGAS,

(SELECT COUNT(*) FROM transaccionesrr WHERE Tipo = 'RECARGA' AND Status = 'PROCESADA' AND Fecha_Recarga BETWEEN PRM_Fecha_Desde AND  PRM_Fecha_Hasta AND Banco_Recarga = PRM_Banco AND RefVerif = '1') AS VERIFICADAS,

(SELECT COUNT(*) FROM transaccionesrr WHERE Tipo = 'RECARGA' AND Status = 'PROCESADA' AND Fecha_Recarga BETWEEN PRM_Fecha_Desde AND  PRM_Fecha_Hasta AND Banco_Recarga = PRM_Banco  AND RefVerif = '0') AS NOVERIFICADAS,

(SELECT COUNT(*) FROM transaccionesrr WHERE Tipo = 'RECARGA' AND Status = 'PROCESADA' AND Fecha_Recarga BETWEEN PRM_Fecha_Desde AND  PRM_Fecha_Hasta AND Banco_Recarga = PRM_Banco  AND RefVerif = '1' AND MontoVerif = '0') AS MONTOVERIF,

(SELECT COUNT(*) FROM transaccionesrr WHERE Tipo = 'RECARGA' AND Status = 'PROCESADA' AND Fecha_Recarga BETWEEN PRM_Fecha_Desde AND  PRM_Fecha_Hasta AND Banco_Recarga = PRM_Banco  AND RecargaDuplicada = '1') AS DUPLICADAS

FROM transaccionesrr WHERE Tipo = 'RECARGA' AND Status = 'PROCESADA' AND Fecha_Recarga BETWEEN PRM_Fecha_Desde AND  PRM_Fecha_Hasta AND Banco_Recarga = PRM_Banco ;

Im trying to do this because i think it will be faster if you think there is a better way i will be appreciated.

I have been trying to make a solution for days but i don't find any

Upvotes: 3

Views: 1251

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use conditional aggregation:

SELECT COUNT(*) AS RECARGAS,
       SUM(RefVerif = '1') AS VERIFICADAS,
       SUM(RefVerif = '0') AS NOVERIFICADAS,
       SUM(RefVerif = '1' AND MontoVerif = '0') AS MONTOVERIF,
       SUM(RecargaDuplicada = '1') AS DUPLICADAS
FROM transaccionesrr
WHERE Tipo = 'RECARGA' AND Status = 'PROCESADA' AND
      Fecha_Recarga BETWEEN PRM_Fecha_Desde AND PRM_Fecha_Hasta AND
      Banco_Recarga = PRM_Banco ;

Upvotes: 2

Related Questions