Sure, I'd expect that to work as well (well, DISTINCT has to be added to the inner select). However, I do think my suggestion normally would be simpler if you know the data - you delete the parts that aren't needed and end up with e.g.
select count( distinct PRO || '_' || FA ) ProFaCount from Tab where Key = :Key Den man. 16. jul. 2018 kl. 09:27 skrev Omacht András [email protected] [firebird-support] <[email protected]>: > > > Hi Set, > > > isn't > > > select count(1) > from (select pro, fa > from tab > where Key = :Key > group by pro, fa) > into :ProFaCount > > > enough? > > > András > > > ------------------------------ > *Feladó:* [email protected] < > [email protected]>, meghatalmazó: Svein Erling Tysvær > [email protected] [firebird-support] <[email protected]> > *Elküldve:* 2018. július 16. 9:11 > *Címzett:* [email protected] > *Tárgy:* Re: [firebird-support] get number of combinations of to keys > > > > Well, you need to know more about PRO and FA than we do, but if they are > character fields and neither of them can include _, then maybe: > > select count( distinct coalesce( PRO, '' ) || '_' || coalesce( FA, '' ) ) > ProFaCount > from Tab > where Key = :Key > > would work. The COALESCE is there in case the fields could be NULL. If PRO > and FA are non-null numbers and FA always is between 0 and 99, then the > query would be > > select count( distinct ( 100 * PRO ) + FA ) ProFaCount > from Tab > where Key = :Key > > HTH, > Set > > Den man. 16. jul. 2018 kl. 08:58 skrev [email protected] > [firebird-support] <[email protected]>: > >> >> >> Hi, >> >> >> Is there a way to get this ProFaCount in one statement (without for >> select) >> >> >> ProFaCount = 0; >> for >> select distinct PRO, FA >> from Tab >> where Key = :Key >> into :PRO, :FA >> do >> ProFaCount = ProFaCount + 1; >> >> Thank you for your help. >> >> >> Regards, >> >> Josef >> >> >> > > __________ Information from ESET Mail Security, version of virus signature > database 17721 (20180716) __________ > > The message was checked by ESET Mail Security. > http://www.eset.com > > > __________ Information from ESET Mail Security, version of virus signature > database 17721 (20180716) __________ > > The message was checked by ESET Mail Security. > http://www.eset.com > > > >
