| 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
DA Morgan wrote:
> pankaj_wolfhunter_at_yahoo.co.in wrote:
> > 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
> > )
> > GROUP BY FIRM;
> >
> > 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
> > )
> > GROUP BY FIRM;
> >
> > Any idea?
> >
> > DB Version:
> > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
> > PL/SQL Release 10.2.0.1.0 - Production
> >
> > TIA
> >
>
I checked the query as Michael suggested for one firm but couldnt able to match the output:
SELECT sum(case when grp = 1 then LINK else 0 end) col1,
sum(case when grp = 2 then LINK else 0 end) col2
FROM (
SELECT TAB2.BADGE,
(case when TAB1.equote in (1,2,3,4) then 1
when TAB1.equote in (5,6) then 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'
AND TAB1.FIRM = 'A'
AND TAB2.FIRM = 'A'
GROUP BY TAB2.BADGE,
(case when TAB1.equote in (1,2,3,4) then 1
when TAB1.equote in (5,6) then 2
end)
)
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' AND TAB1.FIRM = 'A' AND TAB2.FIRM = 'A' GROUP BY TAB1.FIRM, TAB2.BADGE
OUTPUT
COL1 : 52
SELECT NVL(SUM(LINK),0) EQUOTES
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.EQO = 0 AND TAB1.P_TDATE = '20-JAN-2006' AND TAB2.P_TDATE = '20-JAN-2006' AND TAB1.FIRM = 'A' AND TAB2.FIRM = 'A' GROUP BY TAB1.FIRM, TAB2.BADGE
OUTPUT
COL2 : 0
The reason I can see for COL1 mismatch is:
As an independent query there aint any GROUP BY on EQUOTE column in
inline view
whereas when combined, EQUOTE is used in GROUP BY clause.
Daniel, How can we use UNION or UNION ALL in this case?
It would be helpful if more help can be provided in this case.
TIA Received on Mon Nov 06 2006 - 05:04:27 CST
 |
 |