Re: same statement, different filter condition

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 2 Nov 2006 09:53:16 -0800
Message-ID: <1162489996.705766.146670@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. Received on Thu Nov 02 2006 - 11:53:16 CST