Hi Marcin!

I haven't tested, but could something like

with tempkr(one, KONTENER_TOW_ID) as
(select distinct 1, KR.KONTENER_TOW_ID
FROM MG_KONTRAKT_REZ KR
WHERE KR.ODBIORCA_ID = 'ODB')

SELECT KD.DATA_DOST
,T.KOD
,tkr.one REZ_CM
,COUNT (KTOW.KONTENER_ID)
FROM MG_KONTRAKT_KONTENERY_TOW KTOW
INNER JOIN MG_KONTRAKT_KONTENERY KT ON (KTOW.KONTENER_ID =
KT.KONTENER_ID AND KT.STATUS >= 0)
LEFT JOIN MG_INDEKS T ON (T.ID = KTOW.TOWAR_ID)
LEFT JOIN MG_KONTRAKT_DOST KD ON (KTOW.DOST_ID = KD.DOST_ID)
LEFT JOIN tempkr TKR on TKR.KONTENER_TOW_ID = KTOW.ID
GROUP BY 1,2,3

work if you use a sufficiently new Firebird version (possibly adding
something for KR.ODBIORCA_ID, I'm not used to selecting a field within
an EXISTS and may be wrong in assuming it doesn't matter whether the
field holds a value or not)? I'm pretty certain that it is possible to
write a statement that would get the result you want, but not knowing
about your data, I have no clue about performance.

HTH,
Set

Marcin Bury wrote:

> Paul
>
> Thank you for you effort but I need separate count for these
> KTOW.KONTENER_ID where record in MG_KONTRAKT_REZ exist or not:
>
> Sample result
>
> DATA_DOST KOD REZ_CM _COUNT_
> 22.11.2008 TILAPIA <null> 7 <- count where record meeting criteria does
> NOT exist in MG_KONTRAKT REZ
> 22.11.2008 TILAPIA 1 3 <- count where record meeting criteria DOES
> exist in MG_KONTRAKT REZ
>
> I wonder if it is possible to get it working, if not I will have to
> count them 'manually' in my application
>
> Marcin
>
> Mercea Paul wrote:
>>> Hello All,
>>>
>>> I have following query:
>>>
>>> SELECT KD.DATA_DOST
>>> ,T.KOD
>>> ,(SELECT 1 FROM RDB$DATABASE WHERE
>>> EXISTS(SELECT KR.ODBIORCA_ID FROM MG_KONTRAKT_REZ KR WHERE
>>> KR.KONTENER_TOW_ID = KTOW.ID AND KR.ODBIORCA_ID = 'ODB')) REZ_CM
>>> ,COUNT (KTOW.KONTENER_ID)
>>>
>>> FROM MG_KONTRAKT_KONTENERY_TOW KTOW
>>> INNER JOIN MG_KONTRAKT_KONTENERY KT ON (KTOW.KONTENER_ID =
>>> KT.KONTENER_ID AND KT.STATUS >= 0)
>>> LEFT JOIN MG_INDEKS T ON (T.ID = KTOW.TOWAR_ID)
>>> LEFT JOIN MG_KONTRAKT_DOST KD ON (KTOW.DOST_ID = KD.DOST_ID)
>>> GROUP BY 1,2,3
>>>
>>> What should be the proper 'group by' part to get this query working - is
>>> it possible at all?
>>>
>>> I can provide additional info about tables structure when needed.
>>>
>>> TIA
>>> Marcin
>> [PM]
>> Try
>> SELECT KD.DATA_DOST
>> ,T.KOD
>> ,COUNT (KTOW.KONTENER_ID)
>>
>> FROM MG_KONTRAKT_KONTENERY_TOW KTOW
>> INNER JOIN MG_KONTRAKT_KONTENERY KT ON (KTOW.KONTENER_ID = KT.KONTENER_ID )
>> LEFT JOIN MG_INDEKS T ON (T.ID = KTOW.TOWAR_ID)
>> LEFT JOIN MG_KONTRAKT_DOST KD ON (KTOW.DOST_ID = KD.DOST_ID)
>> where KT.STATUS >= 0 and EXISTS(SELECT KR.ODBIORCA_ID FROM MG_KONTRAKT_REZ
>> KR WHERE
>> KR.KONTENER_TOW_ID = KTOW.ID AND KR.ODBIORCA_ID = 'ODB')
>> GROUP BY 1,2
>> [PM]
>> Regards,
>> Paul