Please help with the request. The table has entries with a specific flag and without it. Can one query select the number of records with a flag and the number of records without it (or the number of records in the table)?
For example: you need to select the number of records with even id and the number of records with odd id.

    3 answers 3

    SELECT SUM( IF(field_flag, 1, 0) ) AS flag_exist, SUM( IF(field_flag, 0, 1) ) AS flag_not_exist FROM table
    • That is necessary, thank you) - ling
    • Please - KiTE

    COUNT(expr)

    Returns to the number of values returned by the SELECT statement. The result is a BIGINT value.

    NULLIF(expr1, expr2)

    Returns NULL if expr1 = expr2 is true, otherwise returns expr1. CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

    Accordingly, the query with a sample of the number of even and odd id:

     SELECT COUNT(NULLIF(id % 2, 1)) AS odd, COUNT(NULLIF(id % 2, 0)) AS even FROM table

    Check:

     mysql> CREATE TEMPORARY TABLE test (id integer); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO test VALUES (1), (3), (5), (7), (9), (10), (12); Query OK, 7 rows affected (0.03 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(NULLIF(id % 2, 1)) AS odd, COUNT(NULLIF(id % 2, 0)) AS even FROM test; +-----+------+ | odd | even | +-----+------+ | 2 | 5 | +-----+------+ 1 row in set (0.02 sec)
    • As I understand it, this query will not return quantities, but will return many rows with zeros and ones that still need to be summed. - cy6erGn0m
    • No, this query will return exactly the amount in one line. Count () is an aggregation function, respectively, if the group by is not specified, the aggregation will be on all rows. Only in this case, the aggregation is not on the field, but on the expression. - Ilya Pirogov

    Maybe not the best example, but try this

     select (select count(id) from table where id%2 = 0) as 'Четные', (select count(id) from table where id%2 > 0) as 'Нечетные'

    And another option

     select count(id) from table where id%2 = 0 union all select count(id) from table where id%2 > 0

    In the second version will be in two lines.