Returning values for all rows with COUNT

Question

Post reply

Returning values for all rows with COUNT

Jeff Prater

Newbie

Points: 9
More actions
July 12, 2006 at 8:59 am

#113721

Hi. This is my first time posting. I'm not new to SQL, but I'm not an expert either--I'm still learning [] I have a query where I need to COUNT the rows resturned from the query in order to develop a report. However, my problem is if no rows are returned for a particular individual, they will not show up at all. Is there a way to input a "0" for an individual which produces no rows? Does that make any sense?
Here is my query:
SELECT DefendantCase.ProsAtty, COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey = DefendantEventPros.VBKey
WHERE (DefendantCase.StatusID=1 OR DefendantCase.StatusID=17) AND (DefendantEventPros.EventID=2 AND DefendantEventPros.EventDate Between '7/1/2006' And '7/12/2006') AND DefendantCase.ProsAtty IN (55,52,27,57,3,50,4,2,54,63,88)
GROUP BY DefendantCase.ProsAtty
This is what my output from this query:
ProsAtty    CountOfProsAtty
3           11
4          4
27          11
50          4
52          1
54        2
55         6
However, if I choose a much larger date range, this is my output:
ProsAtty    CountOfProsAtty
2           4
3           75
4          76
27        163
50         98
52         42
54         43
55        91
57         15
63          9
88          72
With the larger date range, all the individuals display. I want there to be a way where I can use my original query (with the short date range) and get this:
ProsAtty    CountOfProsAtty
2           0
3          11
4          4
27         11
50        4
52        1
54          2
55        6
57        0
63          0
88          0
Is this possible with SQL? BTW, I'm using SQL Server 2000

Viewing 3 posts - 1 through 2 (of 2 total)

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

Bhushan Default port Points: 1427 More actions · Answer 1

SELECT ProsAtty, (SELECT COUNT(1) from DefendantEventPros

where EventDate Between '7/1/2006' And '7/12/2006'

and EventID = 2

and VBKey = DC.VBKey) AS CountOfProsAtty

FROM DefendantCase DC

WHERE DC.StatusID IN (1,17)

and ProsAtty IN (55,52,27,57,3,50,4,2,54,63,88)

Naga-

stax68 SSChampion Points: 11711 More actions · Answer 2

You need to use the table which contains all the ProsAtty entities as the basis of your query, using a left join (LEFT JOIN) to return all records from the prosatty table, along with any matching records from the defendantcase table (which will then be aggregated. The LEFT JOIN you have at present is unnecessary. The outer (right) table is used in the where clause without null checks, so you might as well use an inner join (JOIN).

To apply a condition to an outer table, you need to put the condition in the join, so it is evaluated before NULLS are introduced to fill the gaps from unmatched records. NULLs will always fail an equality test, so the effect of such a test in the WHERE clause is to filter out all reords which don't have a record in the outer table.

SELECT P.ProsAtty, COUNT(D.ProsAtty) AS CountOfProsAtty
FROM ProsAtty P
left join DefendantCase D
on P.ProsAtty = D.ProsAtty
left join DefendantEventPros DP
on D.VBKey = D.VBKey
and D.EventID=2
and D.EventDate Between '7/1/2006' And '7/12/2006')
where D.StatusID in (1,17)
and P.ProsAtty IN (55,52,27,57,3,50,4,2,54)
group by P.ProsAtty

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant