Include count of zero in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Include count of zero in query

Author

Topic

stevenf
Starting Member

3 Posts

Posted - 2010-01-04 : 06:02:39

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).

JobId	ConstructNo	ImportanceScoreBand	ImportanceTypeID
1	9	3	1
1	10	2	1
1	11	2	1
1	12	1	1
1	13	2	1
1	14	2	1
1	15	2	1
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.


ImportanceTypeID	ImportanceBands 
1	1
1	2
1	3
1	4

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:


ConstructNo	ImportanceScoreBand	Count
9	1	7
9	2	15
9	3	34
9	4	0
10	1	24
10	2	54
10	3	0
10	4	102

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 also doesn’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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-04 : 06:04:46

Change INNER JOIN to RIGHT JOIN.

N 56°04'39.26"
E 12°55'05.63"

stevenf
Starting Member

3 Posts

Posted - 2010-01-04 : 06:12:52

Sorry - I forgot to mention that I tried with Right Join , but it doesn't make a difference. In the resultset, the cases with a count of zero are excluded both with an inner join and with right joins. I get a resultset like the following with both types of join:

ConstructNo ImportanceScoreBand Count
9 1 8
9 2 8
9 3 9
9 4 4
10 1 17
10 2 8
10 3 4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-04 : 07:32:29

[code]SELECT c.ConstructNo,
b.ImportanceBands,
COUNT(cib.ConstructNo) AS [Count]
FROM (
SELECT ConstructNo
FROM CompetencyImportanceData
GROUP BY ConstructNo
) AS c
CROSS JOIN (
SELECT ImportanceBands
FROM ImportanceLevelBands
GROUP BY ImportanceBands
) AS b
LEFT JOIN CompetencyImportanceData AS cib ON cib.ConstructNo = c.ConstructNo
AND cib.ImportanceScoreBand = b.ImportanceBands
GROUP BY c.ConstructNo,
b.ImportanceBands[/code]

N 56°04'39.26"
E 12°55'05.63"

stevenf
Starting Member

3 Posts

Posted - 2010-01-04 : 10:27:26

Hi Peso

Many thanks for your suggestion - it 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
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-04 : 10:40:09

See the added part in red above.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources