CODEWITHSUNDEEP
mysqlsqlgroup-byisnullnotnull
Volomike
Volomike

Reputation: 24886

MySQL - How Do I Count Nulls and Not Nulls?

I have a simple table of installs:

If the install_slot is NULL, then it's an available install slot. Not null -- then, used slot. I need to return a result of total installs for a given product and email, as well as a result of used installs for a given product and email. I guess I could do this with two queries, but wondered if there's a SQL way to do it all in one?

I tried the following as a wild guess, but it didn't work.

SELECT
    i1.`prod_code`,
    COUNT(i1.`email`) AS total_installs,
    COUNT(ISNULL(i2.`install_slot`)) AS used_installs
FROM
    `installs` AS i1
JOIN
    `installs` AS i2
ON
    i1.`prod_code` = i2.`prod_code`
WHERE
    i1.`email` = '[email protected]'
GROUP BY
    i1.`prod_code`,i2.`prod_code`

Upvotes: 31

Views: 44270

Answers (2)

Gary Lesperance
Gary Lesperance

Reputation: 189

The solution offered did not work for me. I had to modify as follows:

SELECT prod_code,
       COUNT(NULLIF(email,'')) AS total_installs,
       COUNT(NULLIF(install_slot,'')) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code

Upvotes: 11

dgw
dgw

Reputation: 13646

SELECT prod_code,
       COUNT(email) AS total_installs,
       COUNT(install_slot) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code

COUNT counts NOT NULL values only.

Upvotes: 57

Related Questions