Subject Re: [firebird-support] get number of combinations of to keys
Author Svein Erling Tysvær
Post date 2018-07-16T07:44:36Z
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 aomacht@... [firebird-support] <firebird-support@yahoogroups.com>:


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 setysvar@... [firebird-support] <firebird-support@yahoogroups.com>
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.Gschwendtner@... [firebird-support] <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