I have some data，Is there a simple method?

create table tb_count(
    id int not null auto_increment,
    col1 enum('yes', 'no'),
    col2 enum('yes', 'no'),
    col3 enum('yes', 'no'),
    col4 enum('yes', 'no'),
    primary key(id)
) default charset=utf8;

insert into tb_count values (null, 'yes', 'yes', 'no', 'yes');
insert into tb_count values (null, 'no', 'yes', 'no', 'no');
insert into tb_count values (null, 'no', 'yes', 'no', 'yes');
insert into tb_count values (null, 'no', 'no', 'no', 'no');

select * from tb_count;

enter image description here

# I want to get the following output：
# value col1  col2  col3  col4
# yes    1      3    0      2
# no     3      1    4      2

By the way,I have a more complicated method, I don't want to use union all

My code

SELECT
    'yes' AS 'value',
    SUM( CASE WHEN col1 = 'yes' THEN 1 ELSE 0 END ) AS col1,
    SUM( CASE WHEN col2 = 'yes' THEN 1 ELSE 0 END ) AS col2,
    SUM( CASE WHEN col3 = 'yes' THEN 1 ELSE 0 END ) AS col3,
    SUM( CASE WHEN col4 = 'yes' THEN 1 ELSE 0 END ) AS col4 
FROM
    tb_count
    UNION ALL
SELECT
    'no' AS 'value',
    SUM( CASE WHEN col1 = 'no' THEN 1 ELSE 0 END ) AS col1,
    SUM( CASE WHEN col2 = 'no' THEN 1 ELSE 0 END ) AS col2,
    SUM( CASE WHEN col3 = 'no' THEN 1 ELSE 0 END ) AS col3,
    SUM( CASE WHEN col4 = 'no' THEN 1 ELSE 0 END ) AS col4 
FROM
    tb_count

I Wanted to know if it's good practice to do that and what would be the best way to do that?

CodePudding user response：

You could do it with a single query:

select yesno.yn, 
  sum(if(yesno.yn=c.col1, 1, 0)) as col1,
  sum(if(yesno.yn=c.col2, 1, 0)) as col2,
  sum(if(yesno.yn=c.col3, 1, 0)) as col3,
  sum(if(yesno.yn=c.col4, 1, 0)) as col3
from tb_count c
  join (
    select 'yes' yn
    union
    select 'no'
  ) as yesno
group by yesno.yn;

See SQLFiddle.

CodePudding user response：

You can simplyfy by first defining your expected rows and outer apply your aggregations. One example uses a CTE to simplify:

with v as (
    select 'yes' Value union all select 'no'
)
select value,
    Sum(case when col1=value then 1 else 0 end) col1,
    Sum(case when col2=value then 1 else 0 end) col2,
    Sum(case when col3=value then 1 else 0 end) col3,
    Sum(case when col4=value then 1 else 0 end) col4
from v cross join tb_count
group by value

CodePudding user response：

  SELECT value,
         SUM(col1) AS col1,
         SUM(col2) AS col2,
         SUM(col3) AS col3,
         SUM(col4) AS col4
    FROM (
           SELECT col1 AS value, COUNT(col1) AS col1, 0 AS col2, 0 AS col3, 0 AS col4 FROM tb_count GROUP BY col1
           UNION SELECT col2 AS value, 0, COUNT(col2), 0, 0 FROM tb_count GROUP BY col2
           UNION SELECT col3 AS value, 0, 0, COUNT(col3), 0 FROM tb_count GROUP BY col3
           UNION SELECT col4 AS value, 0, 0, 0, COUNT(col4) FROM tb_count GROUP BY col4
         ) AS T
GROUP BY value
ORDER BY value DESC

CodePudding user response：

Your approach is good, while it might be shortened like this

SELECT 'yes' AS value,
       SUM( col1 = 'yes' ) AS col1,
       SUM( col2 = 'yes' ) AS col2,
       SUM( col3 = 'yes' ) AS col3,
       SUM( col4 = 'yes' ) AS col4 
 FROM tb_count
UNION ALL
SELECT 'no',
       SUM( col1 = 'no' ),
       SUM( col2 = 'no' ),
       SUM( col3 = 'no' ),
       SUM( col4 = 'no' )
 FROM tb_count
ORDER BY value DESC

getting rid of redundant CASE..WHEN expressions and the aliasing for the second query

For MySQL 8.0, it would even be shorter:

WITH t(value) AS
(
 SELECT 'yes' UNION ALL
 SELECT 'no'
)
SELECT value,
       SUM( col1 = value ) AS col1,
       SUM( col2 = value ) AS col2,
       SUM( col3 = value ) AS col3,
       SUM( col4 = value ) AS col4 
 FROM tb_count,
      t
GROUP BY value
ORDER BY value DESC

Demo

CodePudding user response：

This is a other way to do that:

SELECT 
  IF(y.yn=1,'yes','no')
, SUM(y.yn = col1) AS col1
, SUM(y.yn = col2) AS col2
, SUM(y.yn = col3) AS col3
, SUM(y.yn = col4) AS col4
FROM `tb_count` AS t
JOIN (SELECT 1 AS yn UNION ALL SELECT 2)  AS y
GROUP BY y.yn;