Firebird select distinct with count

Question

In Firebird 2.5 I have a table of hardware device events; each row contains a timestamp, a device ID and an integer status of the event. I need to retrieve a rowset of the subset of IDs with non-0 statuses and the number of instances of the non-0 events for each ID, within a specified date range. I can get the subset of IDs with non-0 statuses in the specified date range, but I can't figure out how to get the count of non-0-status rows associated with each ID in the same rowset. I'd prefer to do this in a query rather than a stored proc, if possible.

The table is:

RPR_HISTORY
    TSTAMP    timestamp
    RPRID     integer
    PARID     integer
    LASTRES   integer
    LASTCUR   float

The rowset I want is like

RPRID    ERRORCOUNT
-------------------
18       4
19       2
66       7

The query

select distinct RPRID from RPR_HISTORY
where (LASTRES <> 0)
  and (TSTAMP >= :STARTSTAMP);

gives me the IDs I'm looking for, but obviously not the count of non-0-status rows for each ID. I've tried a bunch of combinations of nested queries derived from the above; all generate errors, usually on grouping or aggregation errors. It seems like a straightforward thing to do but is just escaping me.

userNzKf · Answer

Got it! The query

select rh.RPRID, count(rh.RPRID) from RPR_HISTORY rh 
where (rh.LASTRES <> 0)
  and (rh.TSTAMP >= :STARTSTAMP)
  and rh.RPRID in
(select distinct rd.RPRID from RPR_HISTORY rd where rd.LASTRES <> 0) 
group by rh.RPRID;

returns the rowset I need.

Firebird select distinct with count

1 Answers