CODEWITHSUNDEEP
phpmysql
user978905
user978905

Reputation: 5517

MySQL/PHP count number of columns that are not NULL?

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?

Upvotes: 3

Views: 3616

Answers (5)

user1985553
user1985553

Reputation: 1062

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

Upvotes: 2

Stephen Ostermiller
Stephen Ostermiller

Reputation: 25535

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.

Upvotes: 1

fyr
fyr

Reputation: 20869

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 performance does not matter prefer coalesce.
  • If performance matters you need to benchmark both variants to see which outperforms the other.

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838716

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:

  • COUNT - Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement.
  • COALESCE - Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Upvotes: 2

xkeshav
xkeshav

Reputation: 54050

conceptually NULL means “a missing unknown value” 

SELECT COUNT(*) AS totalRows
FROM tableNAME
WHERE columnName IS NOT NULL

Upvotes: 0

Related Questions