Thread: 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

On 22 February 2013 17:01, Steve Clark <sclark@netwolves.com> wrote:

> 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
>


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
having count(*) > X
order by restarts

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

> 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 even=
t. I would like to show only the rows where the number of events exceeds so=
me 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 or=
der by restarts;
>
> So what I would like to see is only the serial_nos that had more than X r=
estarts.
>
> Any ideas would be appreciated.
>
> --
> Stephen Clark

Could you not do:

Select * from=20
(
select serial_no, count(*) as "restarts" from event_log where event_mesg il=
ike 'system sta%' and event_date > current_date - 7 group by serial_no orde=
r by restarts
)
Where "restarts" > X;

Regards,

Russell Keane
INPS

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

> 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
> having count(*) > X
> order by restarts
I think having is the better option.

On 02/22/2013 11:14 AM, Russell Keane wrote:
>
>
> > 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
>
> > having count(*) > X
>
> > order by restarts
>
> I think having is the better option.
>
Thanks all, didn't know about having - I am noobie with SQL.

--
Stephen Clark