Subquery in count SQL

Question

Subquery in count SQL

Navigation

#1 by (1 votes)

0

I have the following table in SQL:

ID  Descripcion Respuesta
 1   Nombre1      True
 2   Nombre2      True
 2   Nombre2      False
 2   Nombre2      False
 1   Nombre1      True

And I want to get the following in one table:

Descripcion  TotalRegistros  RegistrosSI  RegistrosNo
  Nombre1          2               2           0
  Nombre2          3               1           2

By means of this query I can obtain the total of Records

SELECT Descripcion, COUNT(ID) FROM Tabla GROUP BY Descripcion, ID //Por ejemplo

But I do not know how to add the other columns with data, I know that if I get the same total and total SI in the same table I can get the RegistriesNO, but I do not know how to generate it. Is there any way to have a subquery in the count for example?

sql

asked by EriK 02.03.2018 в 18:04

source

1 answer

How to calculate arrears in mysql SQL - Master in same table with default value - Return the different values

score 1 · Accepted Answer

You could solve it this way:

SELECT  ID,
    Descripcion, 
    COUNT(1) AS 'TotalRegistros', 
    SUM(CASE WHEN Respuesta = 'True'  THEN 1 ELSE 0 END)    AS 'RegistroSi', 
    SUM(CASE WHEN Respuesta = 'False' THEN 0 ELSE 1 END)    AS 'RegistroNO'
    FROM Tabla
    GROUP BY ID, Descripcion

What we do is use a SUM() conditional% based on the answer, if it is "True" we will add 1 in RegistroSi and 0 in RegistroNo . If in fact Respuesta is of type BIT , we will simply compare Respuesta = 1 . COUNT(1) simply adds up all cases.