Page 1 of 1

How To Count How Many Columns Are Not Null

Posted: Fri Apr 06, 2007 9:56 am
by seodevhead
Hey guys,

I have a table like so:

Code: Select all

CREATE TABLE images (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      file_id SMALLINT UNSIGNED NOT NULL,
      img1 VARCHAR(250) DEFAULT NULL,
      img2 VARCHAR(250) DEFAULT NULL,
      img3 VARCHAR(250) DEFAULT NULL,
      img4 VARCHAR(250) DEFAULT NULL,
      img5 VARCHAR(250) DEFAULT NULL,
      PRIMARY KEY (id),
      UNIQUE (file_id)
);
And I am trying to construct a MySQL query like so (in pseudo-speak):

SELECT {look at img1, img2, img3, img4 and img5 and return the 'count' of how many of these columns are NOT NULL} WHERE file_id=5

In other words, I want the query to look at those 5 image columns and count how many of them have values (not null). Does anyone know how this can be done? I'm completely stumped, not even sure if that is possible. Thanks for any help and guidance. Take care.

Posted: Fri Apr 06, 2007 10:01 am
by Benjamin
Maybe something like this..

Code: Select all

SELECT
  SUM(IF(`img1`= null, img1,0)) AS `img1_null_count`,
  SUM(IF(`img2`= null, img2,0)) AS `img2_null_count`
FROM `images`
  WHERE file_id=5 

Posted: Fri Apr 06, 2007 10:23 am
by John Cartwright
Using a normalized table structure, you would simply count the rows belonging to a file_id