在MySQL中计算和分组非空值 [英] Count and group non-empty values in MySQL

问题描述

我需要计算非空（按我的意思是一个包含至少1个字符的字符串）行按特定ID分组。例如。我的数据可能如下所示：

I need to count the non-empty (by which I mean a string containing at least 1 character) rows grouped by a particular ID. Eg. my data might look like this:

form_id   mapping
1         'value_1'
1         ''
1         'value_2'
2         ''
2         NULL
3         'value_3'

b $ b

并且我想计算每个表单的非空值，因此我想要的结果如下所示：

and I want to count the non-empty values for each form, so I want the results to look like this:

form_id   mapping_count
1         2
2         0
3         1

如果空值全部为NULL，我想我可以使用

If the empty values were all NULL, I guess I could use

SELECT form_id，count（mapping）FROM table GROUP BY form_id

SELECT form_id, count(mapping) FROM table GROUP BY form_id

...但这将包括零长度字符串在计数，我不想要。

...but that would include zero-length strings in the count, which I don't want.

我可以使用一个子句只返回在映射列中存在值的行，但是我想返回没有映射的表单ID，因此也不好。

I could use a where clause to only return rows where a value exists in the mapping column, but I want to return the form IDs that have no mappings, so that is no good either.

I

推荐答案

SELECT  form_id, COUNT(NULLIF(TRIM(mapping), ''))
FROM    mytable
GROUP BY
        form_id

这不会计算不包含至少一个非空格字符的记录（包括空格字符串，空字符串和 NULL s）。

This will not count records that don't contains at least one non-whitespace character (this includes whitespace strings, empty strings and NULLs).

如果非空的全空格字符串有效，请使用：

If a non-empty all-whitespace string is valid, use this:

SELECT  form_id, COUNT(NULLIF(mapping, ''))
FROM    mytable
GROUP BY
        form_id

这篇关于在MySQL中计算和分组非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！