Post reply

Return SQL count even if it's zero

  • February 23, 2009 at 8:30 am

    #201480

    Hello,

    I have the query below which will return Col1-Col3 along with the number of occurences of each value of Col2 occurs. The problem is If there were no occurences (NULL value for Col2 for a particular record), I still want Col1-Col3 to be shown and I want the number of occurences to be shown as 0. I thought simply using LEFT JOIN's would do this but apparently not. Anybody have an idea of how this is accomplished?

    Thanks

    SELECT Col1, Col2, Col3,

    COUNT(Col2) AS Number of occurences

    FROM TableA

    LEFT JOIN TableB

    ON TableB.Col4 = TableA.Col4

    LEFT JOIN TableC

    ON TableC.Col5 = TableB.Col5

    GROUP BY Col1, Col2, Col3;

  • February 23, 2009 at 8:42 am

    #948647

    Try using ISNULL(COUNT(col2), 0). That should replace the NULL values with a zero.

    Greg

  • February 23, 2009 at 9:31 am

    #948704

    Well the problem is that i'm not getting any return from any records that don't have a value in all of the columns. If a record has a value in col1 but no values in col2 and col3, then that record is not even included in the return set. It's not that it shows up as NULL in the return set, it doesn't show up at all. I thought that was the purpose of LEFT JOIN's so i'm confused as to why it's functioning this way.

    Thanks for your reply btw.

  • February 23, 2009 at 9:43 am

    #948716

    Is that the ACTUAL query you're using? Which tables are col1, col2 and col3 from?

    something isn't right in here. Also - if you just want to count occurrences, then do a count(*) since it will count without care for nullability.

    If that's not the real query - then post the real query, because I suspect something isn't the same between it and your example query.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 23, 2009 at 9:55 am

    #948732

    I agree with Matt. The pseudo code that you posted doesn't tell us what tables the three columns are from or what the relationships between the tables are, etc. Can you post table schema and sample data?

    Greg

  • February 23, 2009 at 10:20 am

    #948751

    No it's not the actual code. I just substituted the names. I actually figured it out and it's working now. Apparently I needed to change the table order in my JOIN. Instead of selecting from tableA and LEFT JOIN tableB, I changed it to select from tableB and LEFT JOIN tableA and it works properly now. Sorry for the confusion and thanks for you help.

  • February 23, 2009 at 11:08 am

    #948778

    FULL OUTER JOIN would solve it all.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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