How To Count How Many Columns Are Not Null

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

How To Count How Many Columns Are Not Null

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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 
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Using a normalized table structure, you would simply count the rows belonging to a file_id
Post Reply