Reputation: 304
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
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