Doing multiple MySQL counts in one query?

Question

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?

SimonJ · Accepted Answer · 2010-10-16 22:06:58Z

4

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.

edited Oct 16, 2010 at 22:06

answered Oct 16, 2010 at 22:01

SimonJ

21.3k2 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Larry Lustig · Accepted Answer · 2010-10-16 22:03:24Z

0

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;

answered Oct 16, 2010 at 22:03

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Collectives™ on Stack Overflow

Doing multiple MySQL counts in one query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related