SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count To Include Nulls


Count To Include Nulls

Author
Message
ubeauty
ubeauty
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1834
[size="1]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[/size]

Here are the results:
Do not use Building and civil engineering 17
Do not use Building Supplies 9
Do not use Commercial and property development 8
Do not use Commercial and property investment 7
Do not use Construction and land 19
Do Not Use Culture (music, museums, broadcasting etc) 20
Do Not Use Education - grant maintained schools 2
Do Not Use Leisure, entertainment and culture 29
Do not use Property - commercial 12
Do not use Property - investment 13
Do not use Property - residential 11

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 engineering 17
Do not use Building Supplies 9
Do not use Commercial and property development 8
Do not use Commercial and property investment 7
Do not use Construction and land 19
Do Not Use Culture (music, museums, broadcasting etc) 20
Do Not Use Education - grant maintained schools 2
Do Not Use Education - tertiary 0
Do Not Use Leisure, entertainment and culture 29
Do not use Property - commercial 12
Do not use Property - investment 13
Do not use Property - residential 11

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

Thanks in advance
mazzz
mazzz
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 2661
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)
Christopher Stobbs
Christopher Stobbs
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5576 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
Christopher Stobbs
Christopher Stobbs
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5576 Visits: 2233

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
Numbers / Tally Tables

SQL-4-Life
ubeauty
ubeauty
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1834
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

SELECT EV.Value AS 'Ind Type Name', COUNT(EV.ExtraValueID) AS 'Count'
FROM ExtraContact EC LEFT JOIN ExtraValue EV ON EV.ExtraValueID = EC.ExtraValueID
LEFT 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)
OR EV.ExtraValueID IS NULL
ORDER BY EV.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)
JestersGrind
JestersGrind
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3925 Visits: 1585
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



ubeauty
ubeauty
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1834
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?!?
JestersGrind
JestersGrind
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3925 Visits: 1585
It's probably the * in Count(*). Instead, select a column from the table that has the records that you are counting.

Greg



Christopher Stobbs
Christopher Stobbs
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5576 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
ubeauty
ubeauty
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1834
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.

SELECT EV.Value AS 'Name', COUNT(EC.ExtraValueID) AS 'Count'
FROM ExtraValue EV LEFT OUTER JOIN ExtraContact EC ON EV.ExtraValueID = EC.ExtraValueID
WHERE EV.ExtraValueID IN (24699,24701,24706,24707,24710,24713,24734,24779,24740,64346,64350,24760)
GROUP BY EV.Value

And here's the results:
Do not use Building and civil engineering 17
Do not use Building Supplies 9
Do not use Commercial and property development 8
Do not use Commercial and property investment 7
Do not use Construction and land 19
Do Not Use Culture (music, museums, broadcasting etc) 20
Do Not Use Education - grant maintained schools 2
Do Not Use Education - tertiary 0 Smile
Do Not Use Leisure, entertainment and culture 29
Do not use Property - commercial 12
Do not use Property - investment 13
Do not use Property - residential 11
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search