Error using count in a subquery

Question

Error using count in a subquery

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I have a query where I use 1 subquery to get data from several tables. And as a result, it returns a total of 2947 records.

    --CONSULTA INICIAL
SELECT dniEgre,

(SELECT TOP 1 CASE WHEN numeRespu = 1 THEN 'SI' ELSE 'NO' END
FROM OCL.tblDetalleRespuNumero
WHERE idPregun = '201' AND dniUsu = dniEgre collate Modern_Spanish_CI_AI
ORDER BY dniEgre DESC) as Trabaja

FROM OCL.tblEgresado
WHERE dniEgre IN
(SELECT dniUsu collate Modern_Spanish_CI_AI FROM OCL.tblExperiencia);

Now I want to use the count function to count the (SI) and the (NO) from my previous query.

 ---consulta para hallar cuantos si trabajan

SELECT  count ((SELECT numerespu
            FROM OCL.tblDetalleRespuNumero
            WHERE idPregun = '201' AND numeRespu=1 AND dniUsu = dniEgre collate Modern_Spanish_CI_AI) as SiTrabaja)
FROM OCL.tblEgresado
WHERE dniEgre IN
(SELECT dniUsu collate Modern_Spanish_CI_AI FROM OCL.tblExperiencia);

 ---consulta para hallar cuantos NO trabajan

SELECT  count( (SELECT numerespu
            FROM OCL.tblDetalleRespuNumero
            WHERE idPregun = '201' AND numeRespu=0 AND dniUsu = dniEgre collate Modern_Spanish_CI_AI) as NoTrabaja)
FROM OCL.tblEgresado
WHERE dniEgre IN
(SELECT dniUsu collate Modern_Spanish_CI_AI FROM OCL.tblExperiencia);

But when using count in the subqueries I get this error:

'count' is not a recognized built-in function name.

I hope you can help me with this error, to be able to count the records.

sql sql-server tsql

asked by Fred 10.02.2017 в 05:24

source

3 answers

How to add rows to table in Android Java: error when calling JPanel from JFrame

score 1 · Answer 1

The simplest way to tell it by modifying at least your query would be something like that

SELECT a.Trabaja, COUNT(1) AS Cuenta
FROM 
(
        SELECT dniEgre,
        (SELECT TOP 1 CASE WHEN numeRespu = 1 THEN 'SI' ELSE 'NO' END
        FROM OCL.tblDetalleRespuNumero
        WHERE idPregun = '201' AND dniUsu = dniEgre collate Modern_Spanish_CI_AI
        ORDER BY dniEgre DESC) as Trabaja
        FROM OCL.tblEgresado
        WHERE dniEgre IN
        (SELECT dniUsu collate Modern_Spanish_CI_AI FROM OCL.tblExperiencia);
) a
GROUP BY a.Trabaja

The code inside subquery a is the one you have included above. The Account field will give you the number of records for each YES or NO.

score 0 · Answer 2

Close the count before the query

    select count(1) 
      from (SELECT numerespu 
              FROM OCL.tblDetalleRespuNumero 
             WHERE idPregun = '201' 
               AND numeRespu=1
               AND dniUsu = dniEgre collate Modern_Spanish_CI_AI as SiTrabaja
              FROM OCL.tblEgresado 
             WHERE dniEgre IN (SELECT dniUsu collate Modern_Spanish_CI_AI 
                               FROM OCL.tblExperiencia)) x

I have copied the query that you set as an example, just to show you where the count () should go

score 0 · Answer 3

You can get the accounts for the SI and the NO of a single using a count conditional like this:

;with cte as (
    SELECT dniEgre,

    (SELECT TOP 1 CASE WHEN numeRespu = 1 THEN 'SI' ELSE 'NO' END
    FROM OCL.tblDetalleRespuNumero
    WHERE idPregun = '201' AND dniUsu = dniEgre collate Modern_Spanish_CI_AI
    ORDER BY dniEgre DESC) as Trabaja

    FROM OCL.tblEgresado
    WHERE dniEgre IN
    (SELECT dniUsu collate Modern_Spanish_CI_AI FROM OCL.tblExperiencia)
)
select si_count = count(case when Trabaja = 'SI' then 'X' end),
       no_count = count(case when Trabaja = 'NO' then 'X' end),
       total_count = count(*)
  from cte;