same statement, different filter condition

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 2 Nov 2006 07:52:23 -0800
Message-ID: <1162482743.167751.299420@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 Received on Thu Nov 02 2006 - 09:52:23 CST