Re: Simple SQL Query Question

[novicedba]

select decode(emm_freq,0,1,emm_freq) * decode(rec_freq,0,1,rec_freq) value from (select count(emission_freq) emm_freq,count(reception_freq) rec_freq from test where station='STAT2')      VALUE --------        3   I am selecting from a view which as you see has the counts of both the columns I am using decode to replace '0' with '1' , without this step the answer would be improper for example for stat2 the inner view will fetch values 3 and 0 when multiplied will give 0 to overcome this I replace 0 with 1   Hope this serves the purpose     coz I am a novice Oracle Certifiable DBBS ----- Original Message ----- From: ALEMU Abiy To: Multiple recipients of list ORACLE-L Sent: Monday, July 02, 2001 3:30 PM Subject: RE: Simple SQL Query Question For example for a table like this one,   STATION    EMISSION_FREQ   RECEPTION_FREQ     ----------         -------------                --------------                    STAT1              192.5                  193.5                          STAT2              192.5                                                    STAT2              194                                                       STAT1              193.5                  194.5                          STAT2              195                                                           I would like to have a value for STAT1 which is equal to 2*2=4  and for STAT2 equal to 3*1=3 -----Message d'origine----- De : novicedba [mailto:[EMAIL PROTECTED]] Envoy� : lundi 2 juillet 2001 11:15 � : Multiple recipients of list ORACLE-L Objet : Re: Simple SQL Query Question SELECT STATION, EMISSION_FREQ, RECEPTION_FREQ, DECODE(SUBSTR(NVL(TO_CHAR(RECEPTION_FREQ),'NONE'),1,1),'N',EMISSION_FREQ*1,EMISSION_FREQ*2) VALUE FROM TEST;   STATION    EMISSION_FREQ   RECEPTION_FREQ     VALUE ----------         -------------                --------------                    --------- STAT1              192.5                  193.5                          385 STAT2              192.5                                                    192.5 STAT2              194                                                       194 STAT2              193.5                  194.5                          387 STAT2              195                                                       195   I did not understand what you meant by 'And I would like to select the number of frequencies, sort of count(*), for a given station and multiply that value by 2 ' so what I have done is if     the reception_freq is null (that would mean there is only emission_freq ) then     value=emission_freq*1 else     value=emission_freq*1 end if   Hope this is what you wanted   coz I am a novice Oracle Certifiable DBBS ----- Original Message ----- From: ALEMU Abiy To: Multiple recipients of list ORACLE-L Sent: Monday, July 02, 2001 1:00 PM Subject: Simple SQL Query Question I've a table with the following structure and sample data :   Station        Emission_Freq        Reception_Freq --------        -----------------        ------------------ STAT1        192.5                        193.5 STAT2        193    STAT2        194 STAT2        193.5                        194.5 STAT2        195     And I would like to select the number of frequencies, sort of count(*), for a given station and multiply that value by 2 if the station has Emission frequency and reception frequency and multiply by one if the station has only the emission frequency or only the reception frequency.  I don't know how to use a condition in SQL.  I'm sure that it's possible to do it but I don't know how.  Can anybody help me ? ------------------------------------------------------------------------------------ @biy @lemu [EMAIL PROTECTED] Database Administration Engineer France