Volomike Volomike - 3 months ago 6
MySQL Question

MySQL - How Do I Count Nulls and Not Nulls?

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`

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

Comments