Count To Include Nulls

  • I'm trying to find a way to count the number of records that have been marked according to a specfic Industry Type, but the results are misleading;

    SELECT EV.Value AS 'Ind Type Name', COUNT(*) AS 'Count'

    FROM ExtraContact EC INNER JOIN ExtraValue EV ON EC.ExtraValueID = EV.ExtraValueID

    INNER JOIN ExtraField EF ON EV.ExtraFieldID = EF.ExtraFieldID

    GROUP BY EV.ExtraFieldID, EV.Value, EV.ExtraValueID

    HAVING EV.ExtraValueID IN(24699,24701,24706,24707,24710,24713,24734,24779,24740,64346,64350,24760)

    ORDER BY EV.Value

    Here are the results:

    Do not use Building and civil engineering17

    Do not use Building Supplies9

    Do not use Commercial and property development8

    Do not use Commercial and property investment7

    Do not use Construction and land19

    Do Not Use Culture (music, museums, broadcasting etc)20

    Do Not Use Education - grant maintained schools2

    Do Not Use Leisure, entertainment and culture29

    Do not use Property - commercial12

    Do not use Property - investment13

    Do not use Property - residential11

    The problem is the ExtraValueID 24779 doesn't have any records assigned to it so is not showing as 0 as required.

    Is there any way possible to get it to display as below with the empty field included in the results:

    Do not use Building and civil engineering17

    Do not use Building Supplies9

    Do not use Commercial and property development8

    Do not use Commercial and property investment7

    Do not use Construction and land19

    Do Not Use Culture (music, museums, broadcasting etc)20

    Do Not Use Education - grant maintained schools2

    Do Not Use Education - tertiary 0

    Do Not Use Leisure, entertainment and culture29

    Do not use Property - commercial12

    Do not use Property - investment13

    Do not use Property - residential11

    Any help would be much appreciated as i've nearly pulled out what little hair I have left!!

    Thanks in advance

  • Try

    HAVING EV.ExtraValueID IN(24699,24701,24706,24707,24710,24713,24734,24779,24740,64346,64350,24760) OR EV.ExtraValueID IS NULL

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • First I'm not sure why you hae those joins if there are no conditions or fields being returned?

    But you gonna want a left join to start with then put the filed name with in the count([fieldName])

    So it will count 0 for nulls

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • DECLARE @tbl1 TABLe

    (Id INT,

    Name VARCHAR(100))

    INSERT INTO @tbl1

    SELECt 1,'soemthing here' UNION ALL

    SELECT 2,'antoher here' UNION ALL

    SELECT 3,'should have 0 count'

    DECLARE @tbl2 TABLE

    (id int,

    Col2 VARCHAR(10))

    INSERT INTO @tbl2

    SELECT 1,'a' UNION ALL

    SELECT 1,'a' UNION ALL

    SELECT 1,'a' UNION ALL

    SELECT 1,'a' UNION ALL

    SELECT 1,'a' UNION ALL

    SELECT 2,'b' UNION ALL

    SELECT 2,'b' UNION ALL

    SELECT 2,'b'

    SELECT

    a.Name,

    COUNT(b.Col2) as [COUNT]

    FROM @tbl1 a

    LEFT JOIN @tbl2 b ON a.id = b.Id

    GROUP BY

    a.Name

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Many thanks for the replies, i have amended accordingly but the Field that doesn't have any records assigned to it still not appearing as NULL or 0

    SELECTEV.Value AS 'Ind Type Name', COUNT(EV.ExtraValueID) AS 'Count'

    FROMExtraContact EC LEFT JOIN ExtraValue EV ON EV.ExtraValueID = EC.ExtraValueID

    LEFT JOIN ExtraField EF ON EV.ExtraFieldID = EF.ExtraFieldID

    GROUP BYEV.ExtraFieldID, EV.Value, EV.ExtraValueID

    HAVINGEV.ExtraValueID IN (24699,24701,24706,24707,24710,24713,24734,24779,24740,64346,64350,24760)

    OR EV.ExtraValueID IS NULL

    ORDER BYEV.Value

    The ExtraContact tbl contains only two columns ExtraContactID and ExtraValueID which is FK to the ExtraValue tbl which contains the Value (name of the Industry Type)

  • I would use a LEFT OUTER JOIN so that all results are returned regardless if they match or not. And then use ISNULL(COUNT(*), 0) AS 'Count' so that zeros are returned where there isn't any matching records.

    Greg

  • Stil not showing up, would this have anything to do with ANSI settings within Management Studio.

    From what i've read and tried the ISNULL(COUNT(*), 0) AS 'Count' should resolve this but that 0 field still won't appear so was wondering if it wa elsewhere?!?

  • It's probably the * in Count(*). Instead, select a column from the table that has the records that you are counting.

    Greg

  • Please ref to my example that I posted with the test data.

    Simply change the table names and the column names...

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Thanks to all for your advice, not sure why i didn't come here sooner but that would be to easy i supoose. It's good to tax the old grey matter, made the amendments as suggested above and everything went through as required.

    SELECTEV.Value AS 'Name', COUNT(EC.ExtraValueID) AS 'Count'

    FROMExtraValue EV LEFT OUTER JOIN ExtraContact EC ON EV.ExtraValueID = EC.ExtraValueID

    WHEREEV.ExtraValueID IN (24699,24701,24706,24707,24710,24713,24734,24779,24740,64346,64350,24760)

    GROUP BYEV.Value

    And here's the results:

    Do not use Building and civil engineering17

    Do not use Building Supplies9

    Do not use Commercial and property development8

    Do not use Commercial and property investment7

    Do not use Construction and land19

    Do Not Use Culture (music, museums, broadcasting etc)20

    Do Not Use Education - grant maintained schools2

    Do Not Use Education - tertiary0 🙂

    Do Not Use Leisure, entertainment and culture29

    Do not use Property - commercial12

    Do not use Property - investment13

    Do not use Property - residential11

Viewing 10 posts - 1 through 9 (of 9 total)

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