| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: same statement, different filter condition
pankaj_wolfhunter_at_yahoo.co.in wrote:
> DA Morgan wrote:
>
>> pankaj_wolfhunter_at_yahoo.co.in wrote: >>> Michel Cadot wrote: >>>> <pankaj_wolfhunter_at_yahoo.co.in> a écrit dans le message de news: 1162489996.705766.146670_at_k70g2000cwa.googlegroups.com... >>>> >>>> Michel Cadot wrote: >>>> >>>>> <pankaj_wolfhunter_at_yahoo.co.in> a écrit dans le message de news: 1162482743.167751.299420_at_k70g2000cwa.googlegroups.com... >>>>> | Greetings, >>>>> | >>>>> | The requirement for COL1 is "sum of number of distinct link group by >>>>> | badge where quote in 1,2,3,4" >>>>> | and requirement for COL2 is "sum of number of distinct link group by >>>>> | bage where quote in 5,6 >>>>> | >>>>> | I was able to do something as below: >>>>> | >>>>> | SELECT SUM(case when quote in (1,2,3,4) >>>>> | then link >>>>> | else 0 >>>>> | end >>>>> | ) col1, >>>>> | SUM(case when quote in (5,6) >>>>> | then link >>>>> | else 0 >>>>> | end >>>>> | ) col2 >>>>> | FROM ( >>>>> | SELECT tab1.quote, tab2.badge, COUNT (DISTINCT tab2.link) link >>>>> | FROM table1 tab1, >>>>> | table2 tab2 >>>>> | WHERE tab1.ttdate = tab2.ttdate >>>>> | AND tab1.symbol = tab2.symbol >>>>> | AND tab1.equote IN (1, 2, 3, 4, 5, 6) >>>>> | GROUP BY tab1.quote, tab2.badge >>>>> | ) >>>>> | >>>>> | But the GROUP BY is getting applied with "tab1.quote". >>>>> | The requirement only says to GROUP BY BADGE but I cant simply take >>>>> | BADGE >>>>> | in inline view cause QUOTE is getting used in the main outer query. >>>>> | And as I want to calculate "distinct link", I am forced to use quote in >>>>> | group by clause in inline view. >>>>> | >>>>> | Can anyone help me on this? Any way to alter my query? >>>>> | I dont want to go for two separate query to calculte COL1 and COL2 >>>>> | cause both tables contain millions of rows. >>>>> | >>>>> | All I want is to somehow calculate both COL1 and COL2 using "QUOTE" and >>>>> | "sum of number of distinct LINK" but QUOTE should not be getting used >>>>> | in GROUP BY. >>>>> | >>>>> | Any help would be appreciated. >>>>> | >>>>> | TIA >>>>> | >>>>> >>>>> SELECT >>>>> BADGE, >>>>> SUM(case when quote in (1,2,3,4) >>>>> then link >>>>> else 0 >>>>> end >>>>> ) col1, >>>>> SUM(case when quote in (5,6) >>>>> then link >>>>> else 0 >>>>> end >>>>> ) col2 >>>>> FROM ( >>>>> SELECT tab1.quote, tab2.badge, COUNT (DISTINCT tab2.link) link >>>>> FROM table1 tab1, >>>>> table2 tab2 >>>>> WHERE tab1.ttdate = tab2.ttdate >>>>> AND tab1.symbol = tab2.symbol >>>>> AND tab1.equote IN (1, 2, 3, 4, 5, 6) >>>>> GROUP BY tab1.quote, tab2.badge >>>>> ) >>>>> GROUP BY BADGE >>>>> / >>>>> >>>>> Regards >>>>> Michel Cadot >>>> Thanks for the help >>>> >>>> But Michael, there's something missing. >>>> >>>> let me explain: >>>> >>>> We have branches who have different BADGE nos. >>>> Say firm "A" has 6 different badges. So I want a "sum of number of link >>>> for firm A" >>>> i.e one single output for this firm "A" and so on for all others >>>> >>>> Your query would give me 6 different result for the same firm "A" >>>> grouped on BADGE. >>>> >>>> My query can be presented with "branch" as: >>>> >>>> SELECT SUM(case when quote in (1,2,3,4) >>>> then link >>>> else 0 >>>> end >>>> ) col1, >>>> SUM(case when quote in (5,6) >>>> then link >>>> else 0 >>>> end >>>> ) col2 >>>> FROM ( >>>> SELECT tab1.branch, tab1.quote, tab2.badge, COUNT (DISTINCT >>>> tab2.link) link >>>> FROM table1 tab1, >>>> table2 tab2 >>>> WHERE tab1.ttdate = tab2.ttdate >>>> AND tab1.symbol = tab2.symbol >>>> AND tab1.equote IN (1, 2, 3, 4, 5, 6) >>>> GROUP BY tab1.branch, tab1.quote, tab2.badge >>>> ) >>>> >>>> Something like (for COL1): >>>> >>>> SELECT SUM(LINK) COL1 >>>> FROM ( >>>> SELECT TAB1.BRANCH, TAB2.BADGE, COUNT(DISTINCT TAB2.LINK) LINK >>>> FROM TABLE1 TAB1, >>>> TABLE2 TAB2 >>>> WHERE TAB1.SYMBOL = TAB2.SYMBOL >>>> AND TAB2.QUOTE IN (1, 2, 3, 4) >>>> AND TAB1.BRANCH = 'A' -- THIS >>>> IS TO TEST FOR ONE BRANCH >>>> GROUP BY TAB1.BRANCH, TAB2.BADGE >>>> ); >>>> >>>> Output: 181 >>>> >>>> Sorry for not mentioning this before. >>>> >>>> ---------------------------------------------------------- >>>> >>>> I think I don't really understand your issue as for me you just >>>> have to add "branch" in all select and group by clauses. >>>> Maybe if you post a test case (create + insert statements) >>>> with the output you want I'd better understand. >>>> >>>> Regards >>>> Michel Cadot >>> Sorry for the delay Michael. >>> >>> To be more precise, I want the below two SELECT statements to be >>> combined to one. >>> Its making use to same tables with different filter conditions. >>> >>> No of records in TABLE1 = 46697622 >>> No of records in TABLE2 = 92161 >>> >>> SELECT FIRM, >>> SUM(LINK) COL1 >>> FROM ( >>> SELECT TAB1.FIRM, >>> TAB2.BADGE, >>> COUNT(DISTINCT TAB2.LINK) LINK >>> FROM TABLE1 TAB1, >>> TABLE2 TAB2 >>> WHERE TAB1.ID = TAB2.ID >>> AND TAB1.SYMBOL = TAB2.SYMBOL >>> AND TAB1.P_TDATE = TAB2.P_TDATE >>> AND TAB1.EQUOTE IN (1, 2, 3, 4) >>> AND TAB1.ORDERIND <> 0 >>> AND TAB1.EQO = 0 >>> AND TAB1.P_TDATE = '20-JAN-2006' >>> AND TAB2.P_TDATE = '20-JAN-2006' >>> GROUP BY TAB1.FIRM, >>> TAB2.BADGE >>> ) >>> GROUP BY FIRM; >>> >>> SELECT FIRM, >>> SUM(LINK) COL2 >>> FROM ( >>> SELECT TAB1.FIRM, >>> TAB2.BADGE, >>> COUNT(DISTINCT TAB2.LINK) LINK >>> FROM TABLE1 TAB1, >>> TABLE2 TAB2 >>> WHERE TAB1.ID = TAB2.ID >>> AND TAB1.SYMBOL = TAB2.SYMBOL >>> AND TAB1.P_TDATE = TAB2.P_TDATE >>> AND TAB1.EQUOTE IN (5,6) >>> AND TAB1.ORDERIND <> 0 >>> AND TAB1.P_TDATE = '20-JAN-2006' >>> AND TAB2.P_TDATE = '20-JAN-2006' >>> GROUP BY TAB1.FIRM, >>> TAB2.BADGE >>> ) >>> GROUP BY FIRM; >>> >>> Any idea? >>> >>> DB Version: >>> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi >>> PL/SQL Release 10.2.0.1.0 - Production >>> >>> TIA >>> >> UNION OR UNION ALL depending on the possibility of duplicate data. >> -- >> Daniel A. Morgan >> University of Washington >> damorgan_at_x.washington.edu >> (replace x with u to respond) >> Puget Sound Oracle Users Group >> www.psoug.org
SELECT sum(case when grp = 1 then LINK else 0 end) col1,
sum(case when grp = 2 then LINK else 0 end) col2 and
SELECT NVL(SUM(LINK),0) EQUOTES change the first query to:
SELECT sum(case when grp = 1 then LINK else 0 end) col1,
sum(case when grp = 2 then LINK else 0 end) col2,
0 EQUOTES
and the second to:
UNION ALL
SELECT NULL, NULL, NVL(SUM(LINK),0) EQUOTES
and then the columns in both queries are equivalent.
Not sure if it will get you where you want to go as I am not entirely sure where you are heading.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Mon Nov 06 2006 - 12:10:16 CST
 |
 |