Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count To Include Nulls Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:08 AM
Points: 111, Visits: 1,791
[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
Post #653620
Posted Tuesday, February 10, 2009 6:33 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 4:11 AM
Points: 408, Visits: 2,661
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)
Post #653633
Posted Tuesday, February 10, 2009 6:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #653634
Posted Tuesday, February 10, 2009 6:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #653640
Posted Tuesday, February 10, 2009 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:08 AM
Points: 111, Visits: 1,791
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)
Post #653654
Posted Tuesday, February 10, 2009 6:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
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




Post #653655
Posted Tuesday, February 10, 2009 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:08 AM
Points: 111, Visits: 1,791
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?!?
Post #653659
Posted Tuesday, February 10, 2009 7:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
It's probably the * in Count(*). Instead, select a column from the table that has the records that you are counting.

Greg




Post #653667
Posted Tuesday, February 10, 2009 7:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #653719
Posted Tuesday, February 10, 2009 8:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:08 AM
Points: 111, Visits: 1,791
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 :)
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
Post #653796
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse