What is the easiest way to sample the quantity of each type?

Question

MySQL

The following tables are available:
example :
id , user_id , status (1,2,3), type_id
15, 53, 1, 1
16, 53, 1, 3
17, 53, 1, 4
18, 53, 3, 3
19, 53, 3, 3
20, 53, 3, 3

types :
id , code
1, "type 1"
3, "type 3"
4, "type 4"

Get the number of records for this user with status = 1, and also display the number of each type, and select only those with status = 3, example:
"quantity with status 1", "quantity for type 1", "quantity for type 3", "quantity for type 4"
3, 0, 3, 0

A banal version with subqueries came to mind, but it looks too static:

SELECT count(distinct id) "кол-во со статусом 1",
       (
           select count(type_id)
           from example e_1
           where type_id = 1 and user_id = e.user_id and e_1.status = 3
       ) "кол-во для типа 1",
       (
           select count(type_id)
           from example e_3
           where type_id = 3 and user_id = e.user_id and e_3.status = 3
       ) "кол-во для типа 3",
       (
           select count(type_id)
           from example e_4
           where type_id = 4 and user_id = e.user_id and e_4.status = 3
       ) "кол-во для типа 4"
FROM example e
WHERE e.status = 1
GROUP BY user_id
HAVING e.user_id = 53;

SQL - almost pseudo code for an example, but the case is quite real

Answer 1

You almost have a solution, just unbind the user_id in subqueries if you need to get the total number of records by type from the example table.

SELECT count(e.id) "кол-во записей example со статусом 1",
       (
           select count(type_id)
           from example e_1
           where type_id = 1
              and e_1.status = 3
       ) "кол-во для типа 1",
       (
           select count(type_id)
           from example e_3
           where type_id = 3
              and e_3.status = 3
       ) "кол-во для типа 3",
       (
           select count(type_id)
           from example e_4
           where type_id = 4 
            and e_4.status = 3
       ) "кол-во для типа 4"
FROM example e
WHERE e.status = 1
     and e.user_id = 53;

If the resulting statistics are within the same user, then you can use the following construction:

SELECT count(e.id) "кол-во записей example со статусом 1",
             count(case when e.type_id = 1
              and e.status = 3 then 1 end)  "кол-во для типа 1",
       count(case when e.type_id = 3
              and e.status = 3 then 1 end)  "кол-во для типа 3",
     count(case when e.type_id = 4
              and e.status = 3 then 1 end)  "кол-во для типа 4"
FROM example e
WHERE e.status = 1
     and e.user_id = 53;

Option 3.

SELECT count(case when e.status = 1 then 1 end) "кол-во записей example со статусом 1",
             count(case when e.type_id = 1
              and e.status = 3 then 1 end)  "кол-во для типа 1",
       count(case when e.type_id = 3
              and e.status = 3 then 1 end)  "кол-во для типа 3",
     count(case when e.type_id = 4
              and e.status = 3 then 1 end)  "кол-во для типа 4"
FROM example e
where e.user_id = 53

Option 4.

-- Считаем статус 1, игнорируем типы
SELECT 'with_status_1' type_query, null type_id, count(*) cnt
FROM example e
where e.status = 1
and e.user_id = 53
union all
-- Считаем остальные типы со статусом 3
SELECT 'with_another_types' type_query, e.type_id, count(*) cnt
FROM example e
where e.status = 3
and e.user_id = 53
group by e.type_id

Answer 2

True, such a query will return 3 records, with different type_id.
And the total number will need to be summed up independently from these 3 records.

SELECT COUNT(*) AS cnt, type_id
FROM example
WHERE user_id = 53 AND status = 1
GROUP BY type_id