limit based on count(*)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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