| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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> a écrit dans le message de news: 1162738826.505203.240590_at_h54g2000cwb.googlegroups.com...
Michel Cadot wrote:
> <pankaj_wolfhunter_at_yahoo.co.in> a écrit dans le message de news: 1162489996.705766.146670_at_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.
>
> ----------------------------------------------------------
>
> I think I don't really understand your issue as for me you just
> have to add "branch" in all select and group by clauses.
> Maybe if you post a test case (create + insert statements)
> with the output you want I'd better understand.
>
> Regards
> Michel Cadot
Sorry for the delay Michael.
To be more precise, I want the below two SELECT statements to be
combined to one.
Its making use to same tables with different filter conditions.
No of records in TABLE1 = 46697622
No of records in TABLE2 = 92161
SELECT FIRM,
SUM(LINK) COL1
FROM (
SELECT TAB1.FIRM,
TAB2.BADGE,
COUNT(DISTINCT TAB2.LINK) LINK
FROM TABLE1 TAB1,
TABLE2 TAB2
WHERE TAB1.ID = TAB2.ID
AND TAB1.SYMBOL = TAB2.SYMBOL
AND TAB1.P_TDATE = TAB2.P_TDATE
AND TAB1.EQUOTE IN (1, 2, 3, 4)
AND TAB1.ORDERIND <> 0
AND TAB1.EQO = 0
AND TAB1.P_TDATE = '20-JAN-2006'
AND TAB2.P_TDATE = '20-JAN-2006'
GROUP BY TAB1.FIRM,
TAB2.BADGE
)
SELECT FIRM,
SUM(LINK) COL2
FROM (
SELECT TAB1.FIRM,
TAB2.BADGE,
COUNT(DISTINCT TAB2.LINK) LINK
FROM TABLE1 TAB1,
TABLE2 TAB2
WHERE TAB1.ID = TAB2.ID
AND TAB1.SYMBOL = TAB2.SYMBOL
AND TAB1.P_TDATE = TAB2.P_TDATE
AND TAB1.EQUOTE IN (5,6)
AND TAB1.ORDERIND <> 0
AND TAB1.P_TDATE = '20-JAN-2006'
AND TAB2.P_TDATE = '20-JAN-2006'
GROUP BY TAB1.FIRM,
TAB2.BADGE
)
DB Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
TIA
Try something like:
SELECT FIRM,
sum(case when grp = 1 then link end) col1,
sum(case when grp = 2 then link end) col2
FROM (
SELECT TAB1.FIRM,
TAB2.BADGE,
decode (case when tab1.equote in (1,2,3,4) then1 else 2 end) grp,
COUNT(DISTINCT TAB2.LINK) LINK
FROM TABLE1 TAB1,
TABLE2 TAB2,
WHERE TAB1.ID = TAB2.ID
AND TAB1.SYMBOL = TAB2.SYMBOL
AND TAB1.P_TDATE = TAB2.P_TDATE
AND TAB1.ORDERIND <> 0
AND TAB1.P_TDATE = '20-JAN-2006'
AND TAB2.P_TDATE = '20-JAN-2006'
GROUP BY TAB1.FIRM,
TAB2.BADGE,
decode (case when tab1.equote in (1,2,3,4) then1 else 2 end)
)
 |
 |