February 5, 2009 at 3:04 pm
Here is my sample script...
select a.group, a.status, count(b.tickets)
from group_tbl a
left outer join tickets_tbl b
on a.group_id = b.group_id
group by a.group, a.status
returns.......
group status count
HR A 5
Acct A 0
IT I 5
PR I 0
How do I script it to exclude the PR group since it is "I" and has zero count? I tried using the HAVING with a.status in it but it wont allow a non-aggregate.
February 5, 2009 at 3:12 pm
How about this.
SELECT X.group, X.status, X.countOfTickets
FROM (
select a.group, a.status, countOfTickets = count(b.tickets)
from group_tbl a
left outer join tickets_tbl b
on a.group_id = b.group_id
group by a.group, a.status
) X
WHERE NOT (X.status = 'I' AND X.countOfTickets = 0)
I haven't tested it.
February 5, 2009 at 3:32 pm
mucho gracias!
February 8, 2009 at 4:02 am
February 8, 2009 at 10:17 am
Smith,
Just a quick question. Is there a performance difference between using the
WHERE NOT that you are using and
where <> 'I' and <> 0
Thanks,
Fraggle
February 9, 2009 at 2:58 am
Just a quick question. Is there a performance difference between using the
WHERE NOT that you are using and
where <> 'I' and <> 0
Actually
WHERE NOT (X.status = 'I' AND X.countOfTickets = 0)
is equivalent to
WHERE (X.status <> 'I' OR X.countOfTickets <> 0)
not
WHERE (X.status <> 'I' AND X.countOfTickets <> 0)
It's quite easy to make this type of mistake in Boolean logic. The main reason I use the WHERE NOT (...) syntax is to try to reduce the possibility such mistakes. I find this syntax closer to the way I think about the requirements. I expect that the query optimizer would produce the same execution plan whichever syntax you use, so that there is no performance benefit either way.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply