I am selecting a number of database columns from a MySQL database. I would like to count the number of returned MySQL rows from a particular set that is not NULL.
For example...
SELECT p.wavURL1, p.wavURL2, p.wavURL3, p.wavURL4, etc.
Can I count in PHP the number of these that are not NULL?
If I understand your question correctly, this should do what you want:
SELECT COUNT(COALESCE(col1, col2, ..., col16)) AS cnt
FROM yourtable
See the MySQL manual for more details:
This should do it:
SELECT
if(p.wavURL1 is null,0,1)+
if(p.wavURL2 is null,0,1)+
if(p.wavURL3 is null,0,1)+
if(p.wavURL4 is null,0,1) as count
You can write a case statement for each of that returns either 1 or 0 depending on whether or not they are null. Then you can sum the results
SELECT
(
CASE WHEN p.wavURL1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN p.wavURL2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN p.wavURL3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN p.wavURL4 IS NULL THEN 0 ELSE 1 END
) AS num_urls
FROM (
SELECT
'a' AS wavURL1,
'b' AS wavURL2,
'c' AS wavURL3,
NULL AS wavURL4
) AS p
;
This returns num_urls as 3 for me because there are three non-null values in the input.
conceptually NULL means “a missing unknown value”
SELECT COUNT(*) AS totalRows
FROM tableNAME
WHERE columnName IS NOT NULL
You may get the count directly via issuing a SQL statement:
SELECT COUNT (*) FROM table p WHERE
<someconditions>
AND p.wavURL1 IS NOT NULL
AND p.wavURL2 IS NOT NULL
AND p.wavURL3 IS NOT NULL
AND p.wavURL4 IS NOT NULL
Another possibility is the coalesce method. But there are different views on that. Performance may differ between IS NULL (which is not ANSI standard) and coalesce.
See: http://sqlserverperformance.idera.com/tsql-optimization/performance-coalesce-null/
If you want to count it as you state it in PHP itself. You may iterate over every row and increase a count variable by one for each iteration.
I would see the statement approach as preferable though.
