There is a table items ;

 id --- status ------------- 1 --- 0 2 --- 1 3 --- 1 4 --- 0 5 --- 1

It is necessary ONE query, display the number of lines with the status: 0 (there are 2) and the number of lines with 1 stutsom (there are 3). Tried through UNION - but it names the fields not as it should.

 SELECT count(*) AS `status_0` FROM `itemы` WHERE `status` = 0 UNION SELECT count(*) AS `status_1` FROM `itemы` WHERE `status` = 1

    3 answers 3

    If I understood correctly, then you need to get one line with two columns? And two lines with one column does not suit you. Then it is done like this.

     SELECT SUM(`status_0`) AS `status_0`, SUM(`status_1`) AS `status_1` FROM ( SELECT count(*) AS `status_0`, 0 AS `status_1` FROM `items` WHERE `status` = 0 UNION SELECT 0 AS `status_0`, count(*) AS `status_1` FROM `items` WHERE `status` = 1 )

    But I would do that.

     SELECT status, COUNT(*) FROM items GROUP BY status
    • The second option came up perfectly - user190134
     select sum(case status when 0 then 1 else 0 end) status_0, sum(case status when 1 then 1 else 0 end) status_1 from items
    • Then select sum (status = 0) status_0, sum (status = 1) status_1 from items CASE what for is needed? - Akina
    • Because this code will work practically under all DBMS. - msi
    • What for? The question has a MySQL tag. - Akina
    • one
      I gave a solution that works under MySQL. - msi

    The fields in the table are named on first request. If I am not mistaken, it may be worth adding the column SELECT 'status_0', count(*) AS constant SELECT 'status_0', count(*) AS cnt FROM items WHERE status = 0 UNION SELECT 'status_1',count(*) AS cnt FROM item WHERE status = 1 if one record is needed

    SELECT count(*) AS cnt_0 , c1.cnt_1 FROM itemы Left join (select count(*) AS cnt_1 from items where status = 1) c1 on 1 = 1 WHERE status = 0