跨越多个列的MySQL COUNT（） [英] MySQL COUNT() across multiple columns

问题描述

我一直在对这个东西打了一会儿，但现在似乎无法使它工作：（

我有一个表格，除了其他标准字段，还有一些字段用于不同的同一类型的值（例如INT）。每个字段的值是唯一的 - >意义，如果它出现在 val_1 不出现在 val_2 和 val_n 中，可以在下一行重复，我的表看起来像这样： / p>

  | id | val_1 | val_2 | val_3 | ... | val_n | some ... | 
  

我的问题是：如何计算表中某些值的出现次数？例如：我想知道

$

  SELECT 
 COUNT（DISTINCT val_1）AS val_1_count，
 COUNT（DISTINCT val_2）AS val_2_count，
 ... 
 FROM ... 
  

$ b $

  SELECT val_1，count（*）as val_1_count 
 FROM ... 
 GROUP BY val_1 
  

一个值。您可以使用UNION在单个（kludgy）查询中对val_1到val_n重复此操作。

如果您希望对所有字段计数，您需要

  SELECT val，count（*）as valcount 
 FROM（
 SELECT val_1 AS val FROM ... 
 UNION ALL 
 SELECT val_2 AS val FROM ... 
 ... 
）AS baseview 
 GROUP BY val 
  

I have been banging my head over this one for quite a while now but can not seem to get it working :(

I have a table which among other standard fields, also has a few fields for different values of a same kind (INT for example). Value of each field is unique -> meaning if it appears in a val_1 it will not appear in val_2 and val_n. It can be however repeated in next row. My table looks something like this:

| id | val_1 | val_2 | val_3 | ... | val_n | some other stuff | ... |

My question is: How can I count occurrences of certain values in my table? For example: I would like to know which value occurs the most.

解决方案

SELECT
  COUNT(DISTINCT val_1) AS val_1_count,
  COUNT(DISTINCT val_2) AS val_2_count,
  ...
FROM ...

will give you the counts for each field.

SELECT val_1, count(*) as val_1_count
FROM ...
GROUP BY val_1

will give you the counts for a value. You can use UNION to repeat this for val_1 to val_n in a single (kludgy) query.

If you want the counts over all fields, you need

SELECT val,count(*) as valcount
FROM (
  SELECT val_1 AS val FROM ...
  UNION ALL
  SELECT val_2 AS val FROM ...
  ...
) AS baseview
GROUP BY val

这篇关于跨越多个列的MySQL COUNT（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！