Re: [firebird-support] get number of combinations of to keys
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 aoma...@mve.hu [firebird-support] : > > > 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ó:* firebird-support@yahoogroups.com < > firebird-support@yahoogroups.com>, meghatalmazó: Svein Erling Tysvær > setys...@gmail.com [firebird-support] > *Elküldve:* 2018. július 16. 9:11 > *Címzett:* firebird-support@yahoogroups.com > *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 josef.gschwendt...@quattro-soft.de > [firebird-support] : > >> >> >> 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 > > > >
Re: [firebird-support] get number of combinations of to keys
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ó: firebird-support@yahoogroups.com , meghatalmazó: Svein Erling Tysvaer setys...@gmail.com [firebird-support] Elküldve: 2018. július 16. 9:11 Címzett: firebird-support@yahoogroups.com 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 josef.gschwendt...@quattro-soft.de [firebird-support] mailto:firebird-support@yahoogroups.com>>: 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
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 josef.gschwendt...@quattro-soft.de [firebird-support] : > > > 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 > > > >
[firebird-support] get number of combinations of to keys
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
