Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: same statement, different filter condition

Re: same statement, different filter condition

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Nov 2006 08:54:15 -0800
Message-ID: <1162486453.67944@bubbleator.drizzle.com>


pankaj_wolfhunter_at_yahoo.co.in wrote:
> 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
Use UNION ALL instead.

-- 
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 Thu Nov 02 2006 - 10:54:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US