Counting 3 columns with yes and no values together in MySql-CodePudding

The table has columns English, Spanish and French. Each with yes or no values and i want to count the number of yes and no for each coloumn into one table.

I tried using distinct count but it cannot put all columns under one table

It should appear like this;

Key| French   | Spanish |English|
--—| -------- | ------- |——————-|  
Yes|    45    |    35   |  72   |
No |    27    |    37   |  0    |

CodePudding user response：

You could try a GROUP BY clause.

SELECT `key`, 
  sum(`french`) as french,
  sum(`spanish`) as spanish,
  sum(`english`) as english
FROM `answers` 
GROUP BY `key`;

key     french  spanish  english    
no      3       2        1
yes     1       2        3

Using the following schema

CREATE TABLE `answers` (
  `key` varchar(3) NOT NULL DEFAULT '',
  `french` int(11) NOT NULL,
  `spanish` int(11) NOT NULL,
  `english` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `answers` (`key`, `french`, `spanish`, `english`) VALUES
('yes', 1, 2, 3),
('no', 3, 2, 1);

CodePudding user response：

Assuming that your table that has the three columns English, Spanish and French is called Source_Table, here's a query that does what you want:

select
  'YES' Key,
  (select count(1) from Source_Table where French='YES') French, 
  (select count(1) from Source_Table where Spanish='YES') Spanish,
  (select count(1) from Source_Table where English='YES') English
from Source_Table
Limit 1 
union 
  'NO' Key, 
  (select count(1) from Source_Table where French='NO') French,
  (select count(1) from Source_Table where Spanish='NO') Spanish,
  (select count(1) from Source_Table where English='NO') English
from Source_Table
Limit 1

Hope this helps. Best regards.

CodePudding user response：

It is rather uncommon to use a string column for 'yes' and 'no' instead of a boolean column in MySQL, but well, the query is almost the same here.

You want one result row for 'Yes' and one for 'No', but each row has a mix of yes and no values. So, you need a UNION ALL for the two result rows. The two queries you are unioning go throgh all rows and count the languages conditionally. In standard SQL we would use COUNT(*) FILTER (WHERE french = 'yes') for this, in some DBMS that don't support the FILTER clause, we'd use COUNT(CASE WHEN french = 'yes' THEN 1 END) or SUM(CASE WHEN french = 'yes' THEN 1 ELSE 0 END), but in MySQL this gets even simpler, as true = 1 and false = 0 in MySQL, and we can use a mere SUM(french = 'yes').

The complete query:

select
  'Yes' as yes_or_no,
  sum(french = 'yes') as french,
  sum(spanish = 'yes') as spanish,
  sum(english = 'yes') as english
from mytable
union all
select
  'No' as yes_or_no,
  sum(french = 'no') as french,
  sum(spanish = 'no') as spanish,
  sum(english = 'no') as english
from mytable;