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/
Go to Top of Page

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.
Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2015-04-16 : 15:16:47
No column name was specified for column 1 of 't'.
Go to Top of Page

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/
Go to Top of Page

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.
Go to Top of Page

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]
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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:
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -