Menu
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` 
464
Person. COUNT(expression) returns the number of values in expression, which is a table column name or an expression that evaluates to a column of data. COUNT(expression) does not count NULL values. This query returns the number of non-NULL values in the Name column of Sample.
SELECT COUNT(IF(average IS NULL,1,0)) FROM table; It worked like a charm for me! This answer is very useful and help full.
Not everyone realizes this, but the COUNT function will only include the records in the count where the value of expression in COUNT(expression) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations.
If you want to count the NULL values, you will have to first convert the NULL values to different values and then apply the aggregate function as demonstrated in the following script.
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.
142
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
