MYSQL 获取每列的计数，其中它等于特定值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19913009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL get count of each column where it equals a specific value
提问by dryan93
I recently set up a MYSQL database connected to a form filled with checkboxes. If the checkbox was selected, it would insert into the associated column a value of '1'; otherwise, it would receive a value of '0'.
我最近建立了一个 MYSQL 数据库，该数据库连接到一个充满复选框的表单。如果选中该复选框，则会在关联列中插入值“1”；否则，它将收到值“0”。
I'd like to eventually look at aggregate data from this form, and was wondering if there was any way I could use MYSQL to get a number for each column which would be equal to the number of rows that had a value of '1'.
我想最终查看此表单中的汇总数据，并想知道是否有任何方法可以使用 MYSQL 为每列获取一个数字，该数字等于值为“1”的行数.
I've tried variations of:
我尝试了以下变体：
select count(*) from POLLDATA group by column_name
which was unsuccessful, and nothing else I can think of seems to make sense (admittedly, I'm not all too experienced in SQL).
这是不成功的，我能想到的其他任何事情似乎都没有意义（不可否认，我在 SQL 方面并不是很有经验）。
I'd really like to avoid:
我真的很想避免：
select count(*) from POLLDATA where column_1='1'
for each column (there close to 100 of them). Is there any way to do this besides typing out a select count(*) statement for each column?
对于每一列（其中接近 100 个）。除了为每列键入一个 select count(*) 语句之外，还有什么方法可以做到这一点？
EDIT:
编辑：
If it helps, the columns are 'artist1', 'artist2', ....'artist88', 'gender', 'age', 'city', 'state'. As I tried to explain below, I was hoping that I'd be able to do something like:
如果有帮助，列是“artist1”、“artist2”、....“artist88”、“gender”、“age”、“city”、“state”。正如我在下面试图解释的那样，我希望我能够做这样的事情：
select sum(EACH_COLUMN) from POLLDATA where gender='Male', city='New York City';
(obviously EACH_COLUMN is bogus)
（显然 EACH_COLUMN 是假的）
采纳答案by StephenCollins
If you are just looking for the sheer number of 1's in the columns, you could try…
如果您只是在寻找列中 1 的绝对数量，您可以尝试……
select sum(col1), sum(col2), sum(col3) from POLLDATA
回答by Vishal
SELECT SUM(CASE
WHEN t.your_column = '1' THEN 1
ELSE 0
END) AS OneCount,
SUM(CASE
WHEN t.your_column='0' THEN 1
ELSE 0
END) AS ZeroCount
FROM YOUR_TABLE t
回答by Steve Wellens
Instead of strings why not store actual numbers, 1 or 0.
为什么不存储实际数字，而不是字符串，1 或 0。
Then you could use the sql SUM function.
然后你可以使用sql SUM函数。
回答by Luuk
select count(*) from POLLDATA group by column_name
按 column_name 从 POLLDATA 组中选择 count(*)
I dont think you want to do a count cause this will also count the records with a 0.
我不认为你想做一个计数，因为这也会用 0 来计算记录。
try
尝试
select column_naam,sum(column_name) from POLLDATA group by column_name
or
或者
select column_naam,count(*) from POLLDATA
where column_name <> 0
group by column_name
only adds the 0
只添加 0
回答by fthiella
When the query begins to be a little too complicated, maybe it's because you should think again about your database structure. But if you want to keep your table as it is, you could use a prepared statement that automatically calculates all the sums for you, without specifying every single column:
当查询开始有点过于复杂时，可能是因为您应该重新考虑您的数据库结构。但是如果你想保持你的表原样，你可以使用一个准备好的语句，它会自动为你计算所有的总和，而无需指定每一列：
SELECT
CONCAT(
'SELECT ',
GROUP_CONCAT(CONCAT('SUM(', `column_name`, ') AS sum_', `column_name`)),
' FROM POLLDATA WHERE gender=? AND city=?')
FROM `information_schema`.`columns`
WHERE `table_schema`=DATABASE()
AND `table_name`='POLLDATA'
AND `column_name` LIKE 'artist%'
INTO @sql;
SET @gender := 'male';
SET @city := 'New York';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @gender, @city;
Please see fiddle here.
请在此处查看小提琴。
