MySQL - How Do I Count Nulls and Not Nulls?
I have a simple table of installs:
- prod_code
- 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`
Answer
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.