Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    10

    Question Unanswered: POP CENSUS QUERY PROBLEM ver 4.1 MySQL

    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!

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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).
    Last edited by aschk; 09-20-07 at 05:47.

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ooh, and you also need a GROUP BY clause in your statement in order to do use COUNT

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •