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` = 'example@example.com'
GROUP BY
    i1.`prod_code`,i2.`prod_code`

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? — Volomike, Feb 11 '12 at 23:40
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. — Volomike, Feb 12 '12 at 04:24
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. — Lightness Races in Orbit, Feb 12 '12 at 12:57

score 56 · Accepted Answer · answered Feb 11 '12 at 23:44

56

SELECT prod_code,
       COUNT(email) AS total_installs,
       COUNT(install_slot) AS used_installs
FROM installs
WHERE email='example@example.com'
GROUP BY prod_code

COUNT counts NOT NULL values only.

answered Feb 11 '12 at 23:44

dgw

13,418
11
56
54

Wow. I'm surprised that actually worked. In my mind it seems to me like it wouldn't work, but it does. – Volomike Feb 11 '12 at 23:49
@Lightness Races in Orbit: is it also a bad habit to say 'it worked'? – Monica Heddneck Mar 10 '17 at 22:32

score 9 · Answer 2 · answered Aug 03 '16 at 16:01

9

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='example@example.com'
GROUP BY prod_code

answered Aug 03 '16 at 16:01

Gary Lesperance

169
2
3

3

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 – hbogert Oct 03 '18 at 12:35

MySQL - How Do I Count Nulls and Not Nulls?

2 Answers2