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
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>