在一个查询中执行多个MySQL计数?

I know the below query isn't valid, but I'm just using it as an example as what I'm trying to achieve.

我知道下面的查询是无效的，但是我只是把它作为我想要实现的一个例子。

Basically what I want to do is get a COUNT() of all the rows & then also get a COUNT() of rows with a condition clause in the one query.

基本上，我要做的是获取所有行的COUNT()，然后在一个查询中获取带有条件子句的行COUNT()。

Such as..

如. .

SELECT 
    COUNT(*) AS full_amount,
    COUNT(address IF NOT NULL),
    COUNT(name IF NOT NULL)
FROM
   table;

Now, what I'm trying to find out above is the full count of the table & I'm also trying to find out a count of the rows in the table where the 'address' & 'name' field are NOT NULL. Not where they both are not null, but individually.

现在，我在上面试图找到的是表的全部计数&我还试图找到表中“address”和“name”字段不为空的行数。不是它们都不是空的，而是单独的。

To further explain, this is how it would be done with multiple queries, which I am trying to avoid..

为了进一步解释，这是如何使用多个查询来完成的，我正在尽量避免。

SELECT COUNT(*) FROM table AS amount;

SELECT COUNT(*) FROM table AS amount WHERE address IS NOT NULL;

SELECT COUNT(*) FROM table AS amount WHERE name IS NOT NULL;

Is there any better ways to do this than running multiple queries?

还有比运行多个查询更好的方法吗?

2 个解决方案

#1

You're nearly there - COUNT counts the number of rows where its parameter is non-NULL:

你已经接近了-计数计数计数数的行数，其中它的参数是非空的:

SELECT COUNT(*) AS full_amount,
    COUNT(address) AS has_address,
    COUNT(name) AS has_name
FROM table;

Also see COUNT(DISTINCT ...) to count the number of different non-NULL values.

还请参见COUNT(DISTINCT…)来计算不同非空值的数量。

#2

You can do it using this trick:

你可以使用这个技巧:

 SELECT COUNT(*), 
    SUM(IFNULL(address, 0, 1)) AS address_count, 
    SUM(IFNULL(name, 0, 1)) as name_count
 FROM Table;

#1