Re: SQL, count and group by

From: Conan <conan_at_(no-spam)as-if.com>
Date: Fri, 27 Oct 2000 10:16:20 +0100
Message-ID: <OJbK5.10995$Bw1.6380@news.indigo.ie>

Hi Peter Try this

select

    o.meter_recnum,
    nvl(i.samples,0)
from

    meter o,
    (select count(s.sample_recnum) samples, 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) i

where o.meter_recnum = i.meter_recnum (+)

    and o.checkconsistent =1
/

it should'nt be much slower than your original query, but I'm sure explain plan will give you the details

HTH
Conan

Peter Laursen wrote in message <01c03fef$f870f5a0$2c289a0a_at_apollo>...
>Small addition:
>Table SAMPLE has a colum meter_recnum, which is a foreign key to table
>METER. In SAMPLE (meter_recnum, sampletime) is unique.
>>
>SAMPLE (a few million rows)
>sample_recnum number primary key
>value number
>sampletime date not null (has index)
>meter_recnum number not null references METER(meter_recnum)
>
>unique(meter_recnum, sampletime)
>
>> Thanks
>> Peter Laursen
>>
Received on Fri Oct 27 2000 - 04:16:20 CDT