SQL, count and group by

From: Peter Laursen <ptl_at_edbgruppen.dk>
Date: 27 Oct 2000 08:07:22 GMT
Message-ID: <01c03fec$fc2a0d60$2c289a0a@apollo>

Hi,

I need a query to give me a count of samples between :sampletime_start and :sampletime_stop, for each meter where checkconsistent=1. (See tables below)

The following query does NOT do that, since if there are zero samples between :sampletime_start and :sampletime_stop, I get no row for that meter, where I would like one row with count=0. How do I write an efficient (rulebased) query to get the desired result? Needs to run on 8.0.5.

select count(s.sample_recnum), m.meter_recnum from sample s, meter m

where s.meter_recnum = m.meter_recnum
  and m.checkconsistent = 1
  and s.sampletime between :sampletime_start and :sampletime_stop

group by m.meter_recnum

METER (some hundred rows)
meter_recnum number primary key
checkconsistent number(1) not null

SAMPLE (a few million rows)
sample_recnum number primary key
value number
sampletime date not null (has index)

Thanks
Peter Laursen Received on Fri Oct 27 2000 - 03:07:22 CDT