Блог

Следующее мое table :

 | col_A | col_B |
|-------|-------|
| A     | Yes   |
| B     | No    |
| A     |       |
| A     | Yes   |
| B     |       |
| A     | No    |
| A     | Yes   |
  

Я попробовал следующее:

 SELECT 
col_A, 
(SELECT COUNT(col_B) FROM table WHERE col_B= "Yes") AS Yes_Count, 
(SELECT COUNT(col_B) FROM table WHERE col_B= "No") AS No_Count 
FROM table
GROUP BY col_A;
  

Но это возвращает неправильную таблицу:

 | GROUP_BY_colA | Yes_Count | No_Count |
|---------------|-----------|----------|
| A             | 3         | 1        |
| B             | 3         | 1        |
  

Мой требуемый вывод:

 | GROUP_BY_colA | colA_count | Yes_Count | No_Count |
|---------------|------------|-----------|----------|
| A             | 5          | 3         | 1        |
| B             | 2          |           | 1        |
  

Рассмотрите возможность использования условной агрегации для поворота вашего набора данных. В MySQL вы можете сформулировать это как:

 select
    col_a,
    count(*) col_a_count,
    sum(col_b = 'Yes') yes_count,
    sum(col_b = 'No') no_count
from mytable
group by col_a
  

Использовать conditional aggregation

 SELECT col_A, 
count(*) as colA_count,
count(case when col_B= 'Yes' then 1 end) AS Yes_Count,
count(case when col_B= 'No' then 1 end) AS No_Count
FROM table
GROUP BY col_A
  

Вы можете использовать оператор Case:

 SELECT 
col_A, count(*) as colA_count,
count(case when col_B= 'Yes' then col_B end) AS Yes_Count,
count(case when col_B= 'No' then col_B end) AS No_Count
FROM Table_count
GROUP BY col_A;
  

 select tab1.col_a,tab1.colA_count,tab2.Yes_count,tab3.No_count from (
select col_A, count(*) as colA_count from st group by Col_A)as tab1 inner join
(select col_A, count(*) as Yes_count from st where col_B='Yes' group by Col_A) as tab2 on tab1.col_A=tab2.col_A inner join
(select col_A, count(*) as No_count from st where col_B='No' group by Col_A) as tab3 on tab3.col_A=tab2.col_A;