Post reply

Query help on Coditional Counting

  • April 1, 2008 at 8:22 am

    #188633

    Hello everyone! I need a little help with a query I am trying to get to work... I am writing a report in visual studio 2005. I have a specific column I am trying to COUNT. The deal is, this column is a picklist that has 6 different values. I would like to be able to COUNT this column for each value in order to list all of the total number for each seperately but on the same report. I tried using subselects so that I could specify a where clause on each without making that where clause global, but the problem with that is since I have to use filtered views (since I am writing this report for MS CRM, in order to maintain security MS says you must use filtered views, also enables you to be able to put filters on the report from within CRM) I get the maximum tables joined error. This is the SQL:

    select (select count(new_buildingstatus) from filterednew_codeenforcement

    where new_buildingstatusname = 'abated') as abated,

    (select count(new_buildingstatus) from filterednew_codeenforcement where new_buildingstatusname = 'compliance') as compliance,

    (select count(new_buildingstatus) from filterednew_codeenforcement where new_buildingstatusname = 'court') as court,

    (select count(new_buildingstatus) from filterednew_codeenforcement where new_buildingstatusname = 'demolition/raze') as demo,

    (select count(new_buildingstatus) from filterednew_codeenforcement where new_buildingstatusname = 'non-enforceable') as nonenforce,

    (select count(new_buildingstatus) from filterednew_codeenforcement where new_buildingstatusname = 'work in progress') as inprogress

    from filterednew_codeenforcement

    And with that I get the Maximum number of tables (260) reached error. Any ideas?? Just FYI, this is the exact error:

    Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

    Thanks guys!

  • April 1, 2008 at 8:29 am

    #796526

    Try your query as this instead

    select sum(case when new_buildingstatusname = 'abated' then 1 else 0 end) as abated,

    sum(case when new_buildingstatusname = 'compliance' then 1 else 0 end) as compliance,

    sum(case when new_buildingstatusname = 'court' then 1 else 0 end) as court,

    sum(case when new_buildingstatusname = 'demolition/raze' then 1 else 0 end) as demo,

    sum(case when new_buildingstatusname = 'non-enforceable' then 1 else 0 end) as nonenforce,

    sum(case when new_buildingstatusname = 'work in progress' then 1 else 0 end) as inprogress

    from filterednew_codeenforcement

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • April 1, 2008 at 8:52 am

    #796562

    I'd use Mark's solution if that's what you need. You don't want to write lots of subqueries as you're scanning the table multiple times and will take a big performance hit.

    I'm not sure why you got the max table limit. Are you only showing us part of the query?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 1, 2008 at 9:05 am

    #796577

    Thanks Mark! You guys act fast... whew! Worked like a charm. That is the second time I didn't even think about a case statement. That is a really creative way to get where I need to be.

    It would be a huge performance hit with all of the sub-selects, and I knew there was an easier way I just couldnt think of it. As far as the maximum tables thing, the filtered views in CRM already have a lot of codebehind as far as the sql goes. Those views consist of many tables that are pre-joined for you, in order to be able to get certain columns into the same views from within the application. It looks like with each sub-select it was re-counting all of those joins. There is probably close to 50 tables in any given filtered view. It has to call on various system tables, and for each entity in CRM there are 2-3 tables that make up all of the columns. I think that might be why I was hitting that error. At any rate, thanks much!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply