MySQL - How Do I Count Nulls and Not Nulls?

Question

I have a simple table of installs:

• prod_code
• email
• install_slot

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`

Augur

I do not understand you, Lightness. What part of the sentence with the question mark in it, beginning with "I guess" do you not understand? – Augur

None of it. I'm not talking about that sentence, am I. – Jayejaylene

Huh? I still don't follow you. – Augur

I'm still not finding your comments very constructive. My counterpoint was also upvoted, and within 1 minute of your complaint about my question style being unclear, and 2 minutes of me actually posting the question, someone easily grasped the problem and solved it. I mean, at least I showed what I attempted, and my test case didn't involve a lot of brainpower to comprehend. I mean, come on, you're a C++ programmer -- you've seen harder questions. – Augur

I didn't say the question was difficult; not even once. I was trying to train you out of the poor habit of saying "it didn't work" instead of describing a program behaviour and comparing it to that which you want. A programming question should never contain the words "it didn't work". But I've given up. – Jayejaylene

Answer 1

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.

Builtin

Wow. I'm surprised that actually worked. In my mind it seems to me like it wouldn't work, but it does. – Augur

@Lightness Races in Orbit: is it also a bad habit to say 'it worked'? – Nikolos

Answer 2

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

Hindrance

probably because emails weren't null but actually the empty string. By doing the nullif you transformed the empty strings to nulls. Which consequently count() does not count – Ssr