[MadCoding]

Hi all,

I am trying to clarify the proper syntax for the following statement:

select
ifnull(count(if(cat_id=1,1,null)),0) as count1,
ifnull(count(if(cat_id=2,1,null)),0) as count2,
ifnull(count(if(cat_id=3,1,null)),0) as count3,
ifnull(count(if(cat_id=4,1,null)),0) as count4,
ifnull(count(if(cat_id=5,1,null)),0) as count5,
ifnull(count(if(cat_id=6,1,null)),0) as count6,
ifnull(count(if(cat_id=7,1,null)),0) as count7,
ifnull(count(if(cat_id=8,1,null)),0) as count8,
ifnull(count(if(cat_id=9,1,null)),0) as count9,
ifnull(count(if(cat_id=10,1,null)),0) as count10,
ifnull(count(if(cat_id=11,1,null)),0) as count11,
ifnull(count(if(cat_id=12,1,null)),0) as count12,
ifnull(count(if(cat_id=13,1,null)),0) as count13,
ifnull(count(if(cat_id=14,1,null)),0) as count14,
ifnull(count(if(cat_id=15,1,null)),0) as count15,
ifnull(count(if(cat_id=16,1,null)),0) as count16,
ifnull(count(if(cat_id=17,1,null)),0) as count17,
ifnull(count(if(cat_id=18,1,null)),0) as count18,
from descriptions

Ok this is the query for a piece of Cold Fusion 7.0 code, which is supposed to querying a multi indexed recursive table called "descriptions".

It is looking a column name called "cat_id" where values can only be 1 to 18 (numerical). cat_id is a actually the representation of a category scheme where there are 18 different categories being polled for thier population. However, because the data being inputted into this "descriptions" table, there may be CATEGORY EXCLUSIONS, or in other words, there could be a couple of categories excluded or missed. So when Cold Fusion looks at this issue, it has issues when dealing with NULL result values for a the above specific query.

This issue here is, instead of using ineffecient cold fusion switch statements (slow way of doing things) I am trying to put the work load unto the database as an alternative.

However, this is a little over my head currently, and I was given this mySQL statement as a possible solution to my situation, but I am finding it a bit daunting to figure out why the above statement keeps throwing a mySQL syntax error. I have never really used an advanced IFNULL statement before.

I just cannot see the issue. Can any MySQL guru's shed any light on the following issue?

Cheers and thanx in advance! I WANNA BE A GURU!

a WANNABEAGURU!

[aschk]

Can you post the DDL for the table + a sample of the data contained within.
Doing a count on NULL will ALWAYS return 0 so it makes the IFNULL part of the statement moot.

I await further information so that a solution may be provided (& discussed) for you.

NOTE : DDL stands for data definition language. Thus if you do a "SHOW CREATE TABLE <tablename>" directly into MySQL substituting the <tablename> part for your actual table it should show you the created structure behind the table (easy for a cut and paste job here).

[aschk]

P.s. the syntax error you are getting if for the following line :
ifnull(count(if(cat_id=18,1,null)),0) as count18,

You need to remove the , (comma) at the end before the FROM part of the statement.

[aschk]

Ooh, and you also need a GROUP BY clause in your statement in order to do use COUNT

[r937]

you do not need a GROUP BY if the only things in the SELECT are aggregate functions

however, the workload is optimized when you write the query like this instead --

select cat_id
     , count(*) as cat_count 
  from description
group by cat_id

i can assure you that in coldfusion, manipulating these results is trivial, and there won't be any pesky NULLs, either