SQL Server – Subquery Returned More Than 1 Value Error Solution

sql serversubquery

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

Best Answer

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')

Related Solutions

Sql-server – Subquery returned more than 1 value

Your trigger has no WHERE clause, does not correlate to the row(s) that were just inserted, and doesn't handle the case where multiple rows might be inserted in a single operation (unlike some platforms, in SQL Server a trigger fires per statement, not per row). So it will work exactly once: when you insert the very first row into the projects table. Try the following instead:

CREATE TRIGGER dbo.create_proid -- please always use schema prefix
ON dbo.projects 
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE p
    SET pro_id = CONVERT(VARCHAR(20), c.comp_type)
      + '-' 
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.client_id), 
        CASE WHEN i.client_id < 100000 THEN 5 ELSE 6 END)
      + '-' 
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.id), 
        CASE WHEN i.id < 100000 THEN 5 ELSE 6 END)
  FROM dbo.projects AS p
  INNER JOIN inserted AS i 
  ON p.id = i.id
  INNER JOIN dbo.comp_types AS c
  ON p.comp_type_id = c.comp_type_id;
END

Not sure why you're storing the pro_id value when you can determine it at runtime. Do you also have an update trigger that maintains it when the comp_type_id changes? What if a comp_type is deleted?

Also please always use schema prefix and don't convert to NVARCHAR without specifying a length. And why are you using the text data type? This has been deprecated for ages - you should be using NVARCHAR(MAX) or VARCHAR(MAX) only if these names and descriptions will really exceed 4000/8000 characters (highly unlikely from the naming).

Understanding the given correlated subquery

zgguy's explanation is very good. It is also illustrated nicely in the execution plan (I have used SSMS2014 to generate one):

the inner query shows on the second "row"
nested loops means that for each row in the outer query, inner input in scanned and matching records are selected
filter (=3) is applied on records from previous step

Best Answer

Related Solutions

Sql-server – Subquery returned more than 1 value

Understanding the given correlated subquery

Related Question