Include count of zero in query

Question

Post reply

Include count of zero in query

steven.fleck

SSC Enthusiast

Points: 112
More actions
January 4, 2010 at 5:14 am

#214153

I have a table “CompetencyImportanceData“, which includes the fields JobID, ConstructNo, ImportanceScoreBand, and ImportanceTypeID. For each JobID, the table contains ImportanceScoreBand values for 20 Constructs (ConstructNo – numbers range from 9 to 28).
JobIdConstructNoImportanceScoreBandImportanceTypeID
1931
11021
11121
11211
11321
11421
11521
11641
ImportanceTypeID is a foreign key to the table “ImportanceLevelBands”. This table stores information on which ImportanceBands are associated with each ImportanceTypeID. In my current use of the “CompetencyImportanceData“ table, ImportanceTypeID is “1” for all JobIDs, which means that the ImportanceScoreBand values range from 1 to 4. However, in other use cases, I will have different ImportanceTypeIDs, which means the ImportanceScoreBand values could for example range from 1 to 10.
ImportanceTypeIDImportanceBands
11
12
13
14
For each of the 20 constructs, I need to count the number of jobs that have ImportanceScoreBand values of the ImportanceTypeID (i.e., in this case values 1, 2, 3, and 4). It is important that I also include the cases where the count is 0. So, the resultset needs to include the columns ConstructNo (9-28), ImportanceScoreBand (1-4), and count of number of jobs for the ConstructNo/ImportanceScoreBand. Here is an extract of the resultset I am looking for:
ConstructNoImportanceScoreBandCount
917
9215
9334
940
10124
10254
1030
104102
So far, I have used the query below to get me nearly what I need – it provides the correct count of each ImportanceScoreband by ConstructNo. The problem is that I cannot find out how to return a count of zero when there are no jobs for the Construct/ImportanceScoreBand. I select JobID, ConstructNo, and ImportanceScoreBand from CompetencyImportanceData into a derived table. Then I join the derived table on the ImportanceLevelBands table, and group on dt.ConstructNo, dt.ImportanceScoreBand, and ImportanceLevelBands.ImportanceBands, and do a count of dt.JobID.
I hoped joining in the “ImportanceLevelBands“ table to the derived table would do the trick, but it doesn’t work (right outer join or right join also don’t work).
select dt.ConstructNo, ILB.ImportanceBands, COUNT(dt.JobID) NumJobs
from
(select top 100 percent CID.JobID, CID.ConstructNo, CID.ImportanceScoreBand
from CompetencyImportanceData CID
group by CID.JobID, CID.ConstructNo, CID.ImportanceScoreBand
order by CID.ConstructNo)
AS dt
inner join ImportanceLevelBands ILB
on dt.ImportanceScoreBand = ILB.ImportanceBands
group by dt.ConstructNo, dt.ImportanceScoreBand, ILB.ImportanceBands
order by dt.ConstructNo, dt.ImportanceScoreBand
Any help much appreciated!

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Steven

Please modify the code below to provide sufficient sample data to return your required result set.

-- Set up test data

-- “CompetencyImportanceData“

-- For each JobID, the table contains ImportanceScoreBand values for 20 Constructs

-- (ConstructNo – numbers range from 9 to 28).

CREATE TABLE #CompetencyImportanceData (JobId INT, ConstructNo INT, ImportanceScoreBand INT, ImportanceTypeID INT)

INSERT INTO #CompetencyImportanceData (JobId, ConstructNo, ImportanceScoreBand, ImportanceTypeID)

SELECT 1, 9, 3, 1 UNION ALL

SELECT 1, 10, 2, 1 UNION ALL

SELECT 1, 11, 2, 1 UNION ALL

SELECT 1, 12, 1, 1 UNION ALL

SELECT 1, 13, 2, 1 UNION ALL

SELECT 1, 14, 2, 1 UNION ALL

SELECT 1, 15, 2, 1 UNION ALL

SELECT 1, 16, 4, 1

-- ImportanceTypeID is a foreign key to the table “ImportanceLevelBands”.

-- This table stores information on which ImportanceBands are associated with each ImportanceTypeID.

-- In my current use of the “CompetencyImportanceData“ table, ImportanceTypeID is “1” for all JobIDs,

-- which means that the ImportanceScoreBand values range from 1 to 4.

-- However, in other use cases, I will have different ImportanceTypeIDs,

-- which means the ImportanceScoreBand values could for example range from 1 to 10.

CREATE TABLE #ImportanceLevelBands (ImportanceTypeID INT, ImportanceBands INT)

INSERT INTO #ImportanceLevelBands (ImportanceTypeID, ImportanceBands)

SELECT 1, 1 UNION ALL

SELECT 1, 2 UNION ALL

SELECT 1, 3 UNION ALL

SELECT 1, 4

SELECT c.*, '#', i.*

FROM #CompetencyImportanceData c

INNER JOIN #ImportanceLevelBands i ON i.ImportanceTypeID = c.ImportanceTypeID

-- 32 rows as expected

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Also see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137844

N 56°04'39.16"
E 12°55'05.25"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SwePeso (1/4/2010)
Also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137844

Nice answer, Peso.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Thank you. I thought I should post the link so that no more double unneccessary work is done.

I suggested your approach first, but after som clarifications, I saw that OP meant something else.

So instead of posting same solution, again, I posted the link to my suggestion.

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Well, I have some time left...

SELECTc.ConstructNo,

b.ImportanceBands,

COUNT(cib.ConstructNo) AS [Count]

FROM(

SELECTConstructNo

FROMCompetencyImportanceData

GROUP BYConstructNo

) AS c

CROSS JOIN(

SELECTImportanceBands

FROMImportanceLevelBands

GROUP BYImportanceBands

) AS b

LEFT JOINCompetencyImportanceData AS cib ON cib.ConstructNo = c.ConstructNo

AND cib.ImportanceScoreBand = b.ImportanceBands

GROUP BYc.ConstructNo,

b.ImportanceBands

N 56°04'39.16"
E 12°55'05.25"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SwePeso (1/4/2010)
Thank you. I thought I should post the link so that no more double unneccessary work is done.
I suggested your approach first, but after som clarifications, I saw that OP meant something else.
So instead of posting same solution, again, I posted the link to my suggestion.

Something doesn't quite add up in the OP's requirements description. I'd wager a few pesos that you've nobbled it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

steven.fleck SSC Enthusiast Points: 112 More actions · Answer 7

Chris, Peso - Thanks very much for your help and input.

Chris - I have attached a file with some insert statements to add example data that matches what I'm working with - I had this more easily to hand, but let me know if you find it easier to work with example data in the format you suggested.

Peso - your suggestion looks very promising. However, I can't quite get it to work. I get this error message:

"Column 'c.ConstructNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

See the GROUP BY part above. I added it now.

N 56°04'39.16"
E 12°55'05.25"

steven.fleck SSC Enthusiast Points: 112 More actions · Answer 9

Tremendous - worked perfectly! 🙂

Thanks very much indeed!