0

This is may table. In one column there are daily dates and in the other is a classification.

enter image description here

I use this query to count how many registers are in a month and which ones are C or D, but the subquery can only return one value . How can i fix it?

select FORMAT(fecha, 'yyyy-MM') as FECHAS, COUNT(*) as TOTAL,
(select count(tipo) from prueba where tipo = 'C' group by FORMAT(fecha, 'yyyy-MM')) as CREDITO,
(select count(tipo) from prueba where tipo = 'D' group by FORMAT(fecha, 'yyyy-MM')) as DEBITO
from prueba
where fecha >= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')
2

You didn't specify which database you were using, but in most databases, you can use a CASE expression inside the COUNT aggregate function to accomplish your goal:

select FORMAT(fecha, 'yyyy-MM') as FECHAS,
       COUNT(*) as TOTAL,
       COUNT(CASE WHEN tipo = 'C' THEN 'X' END) as CREDITO,
       COUNT(CASE WHEN tipo = 'D' THEN 'X' END) as DEBITO
from PRUEBA
where fecha >= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.