Count null and not null fields, grouping by another field

Question

Count null and not null fields, grouping by another field

Navigation

#1 by (4 votes)

3

I am building a query that tells me the number of fields that are null and the number of fields that contain some content.

Data . This is my table:

CREATE TABLE opciones ( ID int(11) NOT NULL, 
                        id_pregunta int(50) DEFAULT NULL, 
                        idenc int(50) DEFAULT NULL, 
                        valor int(50) DEFAULT NULL, 
                        hallazgo varchar(250) COLLATE utf8mb4_spanish_ci DEFAULT NULL, 
                        Accion varchar(250) COLLATE utf8mb4_spanish_ci DEFAULT NULL, 
                        fecha date NOT NULL, hora date NOT NULL ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci; 


INSERT INTO opciones (ID, id_pregunta, idenc, valor, hallazgo, Accion, fecha, hora) 
      VALUES(73, 21, 16, 1, NULL, NULL, '0000-00-00', '0000-00-00'),
            (74, 22, 16, 1, NULL, NULL, '0000-00-00', '0000-00-00'),
            (75, 21, 16, 1, 'Se tiene un hallazgo? ', 'Cual es la acciÃ³n correctiva ', '0000-00-00', '0000-00-00'),
            (76, 22, 16, 0, 'Se tiene un hallazgo? ', 'Cual es la acciÃ³n correctiva ', '0000-00-00', '0000-00-00'),
            (77, 21, 16, 1, 'Se tiene un hallazgo? ', 'Cual es la acciÃ³n correctiva ', '0000-00-00', '0000-00-00'),

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla 'respuestas'
--

CREATE TABLE 'respuestas' (
  'id' int(11) NOT NULL,
  'texto' varchar(250) COLLATE utf8mb4_spanish_ci NOT NULL,
  'idenc' int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

--
-- Volcado de datos para la tabla 'respuestas'
--

INSERT INTO 'respuestas' ('id', 'texto', 'idenc') VALUES
(31, 'El personal esta usando el EPP definido en el estÃ', 19),
(32, 'sensores y/o guardas en funcionamiento?', 19),
(33, 'seÃ±alamientos y salidas de emergencia estan en bu', 19),
(34, 'las instrucciones de trabajo estan presentes y dis', 19),
(35, 'la 1era pieza esta identificada y disponible en el', 19),
(36, 'la hoja de mantenimiento autonomo esta actualizada', 19),
(37, 'el operador realiza su operacion de acuerdo a la i', 19),
(38, 'materiales identificados y en su caso segregados', 19),
(39, 'registro de calidad realizados', 19),
(40, 'hay registros de liberaciÃ³n de primera pieza', 19),
(41, 'pieza master / muestras NG - OK disponibles', 19),
(42, 'los indicadores estan posteados y actualizados', 19),
(43, '', 19);

--

Expected result . I want to get 2 fields for each one (one with the total of null and the other with the rest)

I want to group me by question id, adding the amount of null / no null per field.

What did I try?

I have the following, but it does not bring me the fields null :

SELECT a.texto as titulo, 
       SUM(CASE WHEN b.valor = 1 THEN 1 ELSE 0 END) AS "si", 
       SUM(CASE WHEN b.valor = 0 THEN 1 ELSE 0 END) AS "NO", 
       SUM( IF(b.hallazgo IS NOT NULL, 1, 0)), 
       SUM( IF(b.Accion IS NOT NULL, 1, 0)) 
       FROM respuestas a 
       INNER JOIN opciones b 
             ON a.id = b.id_pregunta 
       WHERE a.idenc = 18 
       GROUP BY b.id_pregunta

I only add the fields but it does not separate me by id. Currently he sends me this

campo 1 = 3 accion 3 hallazgo
campo 2 = 3 accion 3 hallazgo 
campo 3 = 3 accion 3 hallazgo

But I want:

campo 1 = 1 accion 1 hallazgo
campo 2 = 0 accion 0 hallazgo 
campo 3 = 2 accion 2 hallazgo

This is the query I tried later:

SELECT a.texto as titulo,b.id_pregunta,
SUM(CASE WHEN b.valor = 1 THEN 1 ELSE 0 END) AS "si",
SUM(CASE WHEN b.valor = 0 THEN 1 ELSE 0 END) AS "NO",
SUM( IF(b.id_pregunta = a.id and b.hallazgo IS NOT NULL, 1, 0)) as hallazgos,
SUM( IF(b.id_pregunta = a.id and b.Accion IS NOT NULL, 1, 0)) as acciones,
SUM( IF(b.id_pregunta = a.id and b.hallazgo IS NULL, 1, 0)) as no hallazgos,
SUM( IF(b.id_pregunta = a.id and b.Accion IS NULL, 1, 0)) as no acciones
FROM respuestas a INNER JOIN opciones b ON a.id = b.id_pregunta WHERE a.idenc = 19
GROUP BY a.id,a.texto,b.id_pregunta

This is the result I currently get:

Question . How can I obtain, for each of the 2 fields ( accion and hallazgo ), the sum of the null s in one column and the sum of the no null in another column, grouping by id_pregunta ?

mysql sql query

asked by antonio sanchez 22.02.2018 в 00:28

source

1 answer

Detect what type of iOS device and android I have a problem with showing values on the screen with javascript

score 4 · Accepted Answer

NULL values mean "I do not have a defined value" and are treated in a particular way, the following example shows how to count:

USE test;

CREATE TABLE foo (
  id              BIGINT(20) NOT NULL AUTO_INCREMENT,
  attribute_name  CHAR(1),
  attribute_value CHAR(1),
  PRIMARY KEY ('id')
);

INSERT INTO foo (attribute_name, attribute_value)
VALUES ('X', NULL),('Y', 'A'),('Y', 'B'),('Z', '');

We insert 4 records, only one is considered NULL and the other three are NOT NULL. Finally we make a SELECT grouping by attribute_name and we make a kind of counter for those that are NULL and NOT NULL.

SELECT
  attribute_name,
  SUM(IF(attribute_value IS NULL, 1, 0)) AS 'Si',
  SUM(IF(attribute_value IS NOT NULL, 1, 0)) AS 'No'
FROM foo
GROUP BY attribute_name;