February 10, 2009 at 6:23 am
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
February 10, 2009 at 6:33 am
February 10, 2009 at 6:34 am
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]
February 10, 2009 at 6:38 am
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]
February 10, 2009 at 6:52 am
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)
February 10, 2009 at 6:53 am
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
February 10, 2009 at 6:57 am
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?!?
February 10, 2009 at 7:01 am
It's probably the * in Count(*). Instead, select a column from the table that has the records that you are counting.
Greg
February 10, 2009 at 7:47 am
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]
February 10, 2009 at 8:33 am
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