Re: same statement, different filter condition

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 2 Nov 2006 17:43:00 +0100
Message-ID: <454a2015$0$17265$426a74cc@news.free.fr>

<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 Received on Thu Nov 02 2006 - 10:43:00 CST