Subquery returned more than 1 value. please help. i beg for mercy This is may table. In one column there are daily dates and in the other is a classification.



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 &gt;= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')
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 &gt;= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')