Re: Filtering records for a Count

From: Ed Prochak <edprochak_at_gmail.com>
Date: 16 Feb 2006 10:04:40 -0800
Message-ID: <1140113080.125076.259800@g47g2000cwa.googlegroups.com>

Robert Scheer wrote:
> Hi.
>
> I have a table with the following fields, among others:
>
> Area status
> 43458 4
> 17866 2
> 67533 4
> 67533 5
> 80236 3
> 59001 4
>
> I use this simple query to count the areas with the status 4:
>
> SELECT area, COUNT(1) AS Rejected FROM areas
> WHERE TRUNC(datesent) >= '16/01/2006'
> AND TRUNC(datesent) <= '16/01/2006'
> AND area <> '00001111'
> AND status = 4
> AND point <> 'ADG'
> GROUP BY area
> ORDER BY area
>
> The problem is that, when the same area appears with the status 4 and
> 5, I can't count this area. In the above table, the area 67533 should
> not be in my total as it appears twice, with 4 and 5. Is it possible to
> do that?
>
> Thanks,
> Robert Scheer

given you tell us nothing about all the other columns in that table or

about the time dependancy, I'll ignore them, except to point out

> WHERE TRUNC(datesent) >= '16/01/2006'
> AND TRUNC(datesent) <= '16/01/2006'
looks equivalent to
 TRUNC(datesent) = '16/01/2006'
to me. (and you are strongly depending on the default date format, AND giving up on any index on datesent. Better would be to change the constant to a data, as in
 WHERE datesent >= TO_DATE('16/01/2006', 'DD/MM/YYYY')  AND datesent <= TO_DATE('16/01/2006', 'DD/MM/YYYY')+1

now for your issue with status. Rephrasing your question may suggest the answer to you, You want all the rows with status 4 where there does not exist another row for that area with a different status. Does that help?

Ed
(final hint: think subquery.) Received on Thu Feb 16 2006 - 12:04:40 CST