Exclude certain record in 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 2012 Forums

Transact-SQL (2012)

Exclude certain record in COUNT(*)

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 14:34:12

Hello,

I'm trying to do a COUNT on a table with the following join.

select count(*)
from arrest a inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W' and b.date is null
and b.comments like '%status changed to: active%')

However, it's a one-to-many relationship. Some of the records in the child table often occur more than once for each 'arrestno'. As you can see, I'm searching for b.date is null. But what I want to do is exclude from the COUNT any records where that particular 'arrestno' is found in another record where the b.date is NOT NULL (i.e. it is populated).

For example, let's say we have this.

ARRESTNO COMMENTS DATE
10 STATUS CHANGED TO: ACTIVE 2/14/2012
10 STATUS CHANGED TO: ACTIVE

I would not want to count '10' in this case because it has another record where the date is populated.

If you could help me with how to do this, I would greatly appreciate it. Thanks so much for any help you could provide.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-16 : 14:35:22

Add a GROUP BY for ARRESTNO.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 14:52:19

I'm sorry Tara. My brain is completely fried at the moment.

I don't follow. When I simply add GROUP BY arrestno I get output like this:

1 1
2 1
3 1
.
.
.
14000 1

With the last record showing the same COUNT received before I added GROUP BY.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-16 : 14:54:57

I don't see how you get that output with just a count(*). Add a group by to your count(*) query.

Or are you not showing us the complete picture? I do see an ending parenthesis without a beginning one, so I suspect there's more to the story.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 15:12:01

Yes I did cut out some apparent unnecessary conditions in order to make it more readable, for when I run this exact query I still just get a long list of 1's.

select count(*)
from arrest a inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W' and b.date is null
and b.comments like '%status changed to: active%'
group by a.arrestno

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-16 : 15:13:12

Then it worked. Now count up those 1s.

select count(*) from (
select count(*)
from arrest a inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W' and b.date is null
and b.comments like '%status changed to: active%'
group by a.arrestno) t

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 15:16:47

No column name was specified for column 1 of 't'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-16 : 15:30:41

Sorry:

select count(*) from (
select count(*) as arrestcount
from arrest a inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W' and b.date is null
and b.comments like '%status changed to: active%'
group by a.arrestno) t

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 15:33:39

Thanks but I get the same result with that as I do with my original.

select count(*)
from arrest a inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W' and b.date is null
and b.comments like '%status changed to: active%'

Both this and your query return a count of 140037.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-16 : 15:37:05

[code]
select COUNT(*)
from (
select a.arrestno
from arrest a
inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W'
and b.comments like '%status changed to: active%'
group by a.arrestno
--count only a.arrestno's that have only null b.date's
having max(case when b.date is null then 0 else 1 end) = 0
) as derived

[/code]

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 15:41:24

Almost Scott. I want to count all arrestno's that have ONLY null b.date's. If it has another record where the date is populated, I do not want to add that one to the total count.

Thanks.

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 16:05:11

I think that does the trick Scott.

Thanks so much to you both.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-16 : 16:05:15

Hmm, that should be exactly what that code does.

Edit: Sorry, didn't see your latest reply until after I posted this:

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 16:06:11

Yes that works. I must have posted my reply just before you edited it.

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 07:35:10

Don't know if helpful, but you can do something like this:


select count(*) AS Total, SUM(CASE WHEN b.date IS NULL THEN 0 ELSE 1 END) AS HasADate
from arrest a inner join arr_com b on a.arrestno = b.arrestno
where a.arresttype = 'W' and b.date is null
and b.comments like '%status changed to: active%'
group by a.arrestno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources