在列中查询多个不同的

I am trying to write a query on a column in a table. In column "text" I can have values of either 5 or 10 or null. I want to get count of number of rows in the table for 5 or 10 or null. I wrote following query it is not working properly

我正在尝试在表中的列上编写查询。在“text”列中，我可以具有5或10的值或null。我想得到表中5或10行的行数或null。我写了以下查询它不能正常工作

select COUNT(*) from t_test
select COUNT(text) from t_test where text=5
select COUNT(text) from t_test where text=10
select COUNT(text) from t_test where text=null

I can get first three select statements values but the last one with null returns zero while there are rows with null in it. How to write this query? Thanks

我可以获得前三个select语句值，但最后一个null返回零，而行中有null。怎么写这个查询？谢谢

2 个解决方案

#1

You should just use conditional summation:

你应该只使用条件求和：

select count(*),
       sum(case when text = '5' then 1 else 0 end) as Num_5,
       sum(case when text = '10' then 1 else 0 end) as Num_10,
       sum(case when text is null then 1 else 0 end) as Num_Null
from t_test;

This is assuming that a field called text is stored as a character string, so the constants are put in quotes. If it is really a number, first I'd be curious why it is called text. In that case, you can dispense with the single quotes.

这假设一个名为text的字段存储为字符串，因此常量放在引号中。如果它真的是一个数字，首先我会好奇为什么它被称为文本。在这种情况下，您可以省去单引号。

In your case, the last one doesn't work because count(text) counts non-null values. But the where clause only keeps NULL values. For that one, you should use count(*). The correct query would be:

在您的情况下，最后一个不起作用，因为count（text）计算非null值。但是where子句只保留NULL值。对于那个，你应该使用count（*）。正确的查询将是：

select count(*) from t_test where text is null;

#2

What you need for that final query is:

您最终查询所需的是：

select COUNT(*) from t_test where text is null

Notice:

注意：

COUNT(*) rather than COUNT(TEXT) which is null, rendering no values
COUNT（*）而不是COUNT（TEXT），它为null，不呈现任何值
is null rather than =null
是null而不是= null

Your final set of queries is thus:

因此，您的最终查询集是：

select COUNT(*) from t_test
select COUNT(text) from t_test where text=5
select COUNT(text) from t_test where text=10
select COUNT(*) from t_test where text is null

#1