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