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
Received on Sun Nov 05 2006 - 11:58:57 CST
