Re: limit based on count(*)

> Hello List,
>
> I have a query that counts how many time an event occurs in our event_log these are grouped by the serial number of the device that created the event. I would like to show only the rows where the number of events exceeds some threshold.
>
> simplified query:
> select serial_no, count(*) as "restarts" from event_log where event_mesg ilike 'system sta%' and event_date > current_date - 7 group by serial_no order by restarts;
>
> So what I would like to see is only the serial_nos that had more than X restarts.
>
> Any ideas would be appreciated.
>
> --
> Stephen Clark

Could you not do:

Select * from
(
select serial_no, count(*) as "restarts" from event_log where event_mesg ilike 'system sta%' and event_date > current_date - 7 group by serial_no order by restarts
)
Where "restarts" > X;

Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk