Doing multiple MySQL counts in one query?

Question

Hey guys..... now 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.

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.

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?

Thanks!

Answer 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.

Answer 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;