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

<pankaj_wolfhunter (AT) yahoo (DOT) co.in> a écrit dans le message de news: 1162482743.167751.299420 (AT) k70g20...oglegroups.com...
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

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
Use UNION ALL instead.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Michel Cadot wrote:

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.

<pankaj_wolfhunter (AT) yahoo (DOT) co.in> a écrit dans le message de news: 1162489996.705766.146670 (AT) k70g20...oglegroups.com...

Michel Cadot wrote:

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

Michel Cadot wrote:
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

<pankaj_wolfhunter (AT) yahoo (DOT) co.in> a écrit dans le message de news: 1162738826.505203.240590 (AT) h54g20...oglegroups.com...

Michel Cadot wrote:
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

----------------------------------------------

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)
)
GROUP BY FIRM;

Regards
Michel Cadot

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
UNION OR UNION ALL depending on the possibility of duplicate data.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:


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)
)
OUTPUT
COL1 : 60
COL2 : 0

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 (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

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
What you have is:

SELECT sum(case when grp = 1 then LINK else 0 end) col1,
sum(case when grp = 2 then LINK else 0 end) col2
and

SELECT NVL(SUM(LINK),0) EQUOTES

change the first query to:

SELECT sum(case when grp = 1 then LINK else 0 end) col1,
sum(case when grp = 2 then LINK else 0 end) col2,
0 EQUOTES

and the second to:

UNION ALL
SELECT NULL, NULL, NVL(SUM(LINK),0) EQUOTES

and then the columns in both queries are equivalent.

Not sure if it will get you where you want to go as I am not entirely
sure where you are heading.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org