Returning values for all rows with COUNT

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 2000 Forums

Transact-SQL (2000)

Returning values for all rows with COUNT

Author

Topic

bla4free
Starting Member

10 Posts

Posted - 2006-07-12 : 10:21:53

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)

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-12 : 10:24:23

You need to incorporate a "case" clause in/near your "count" clause...covering nulls in the "DefendantEventPros.VBKey" column.

Search here (and BOL) for examples of this.

bla4free
Starting Member

10 Posts

Posted - 2006-07-12 : 10:45:58

What do you mean? I know none of our information has null values b/c the web app will not allow null values. All I want to do is display how many rows are returned per attorney. If the attorney did not have any rows returned, I want it to display a 0 by their name. I tried your CASE clause, but it resulted NULL for every attorney, and still only displayed the first few attorneys. Thanks for your help!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 10:51:54

SELECT a.ProsAtty,
sum(case when DefendantCase.ProsAtty is null then 0 else 1 end) AS CountOfProsAtty
FROM
(select distinct ProsAtty from DefendantCase) a
left join DefendantCase
on DefendantCase.ProsAtty = a.ProsAtty
and (DefendantCase.StatusID=1 OR DefendantCase.StatusID=17)
AND (DefendantEventPros.EventID=2
LEFT JOIN DefendantEventPros
ON DefendantCase.VBKey = DefendantEventPros.VBKey
GROUP BY a.ProsAtty

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 10:55:28

You shold place the condition for the DefendantEventPros in the ON statement rather than WHERE


SELECT DefendantCase.ProsAtty, COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM   DefendantCase LEFT JOIN DefendantEventPros 
       ON  DefendantCase.VBKey = DefendantEventPros.VBKey
       AND (DefendantEventPros.EventID = 2 
            AND DefendantEventPros.EventDate Between '7/1/2006' And '7/12/2006') 
WHERE (
          DefendantCase.StatusID=1 
       OR DefendantCase.StatusID=17
       ) 
AND    DefendantCase.ProsAtty IN (55,52,27,57,3,50,4,2,54)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 11:08:26

> I'm not an expert either--I'm still learning

I know of no expert that isn't

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

bla4free
Starting Member

10 Posts

Posted - 2006-07-12 : 11:26:25

OK. I tried these queries and they are inflating my numbers dramaticly. When I inserted the EvenetDate I ended back up with the same results as before. I guess there's not a way to do this. :(

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-12 : 11:36:02

How do you know the numbers are inflated and not accurate?

EDIT: and i question your output. How did you get


ProsAtty    CountOfProsAtty
2           0
3	    11
4	    4
27	    11
50	    4
52	    1
54	    2
55	    6
57	    0
63          0
88          0

when you have this clause:


DefendantCase.ProsAtty IN (55,52,27,57,3,50,4,2,54)

88,63 are not in your IN statement, but show up in your result set?

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

bla4free
Starting Member

10 Posts

Posted - 2006-07-12 : 12:08:18

quote:
Originally posted by DonAtWork

How do you know the numbers are inflated and not accurate?

EDIT: and i question your output. How did you get

ProsAtty CountOfProsAtty
2 0
3 11
4 4
27 11
50 4
52 1
54 2
55 6
57 0
63 0
88 0

when you have this clause:

DefendantCase.ProsAtty IN (55,52,27,57,3,50,4,2,54)

88,63 are not in your IN statement, but show up in your result set?

I was making changes to some of the paramters of the report to display more attorneys. I forgot to change my IN clause.

I know my original query is accurate because it matches our paper records (for legal reasons).

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 13:31:31

Are you sure?
Can't see why the query I gave should give a different result to yours except by giving a 0 count to the entries yours doesn't contain.

Try this
select x.ProsAtty, CountOfProsAtty = coalesce(x.CountOfProsAtty,0)
from
(select distinct ProsAtty from DefendantCase) a
left join
(
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)
GROUP BY DefendantCase.ProsAtty
) x
on x.ProsAtty = a.ProsAtty

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-13 : 08:12:30

"null"s will be returned for any missing values on a LEFT JOIN....which ties in with your statement "However, my problem is if no rows are returned for a particular individual, they will not show up at all."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources