We help IT Professionals succeed at work.

Using grouping and sub groups in oracle query

mahjag
mahjag asked
on
Medium Priority
365 Views
Last Modified: 2012-03-14
Hi

I have a requirement for using groups and sub groups more like analytical partition by clause queries in oracle that I may need some help

the data from a table is like this

group_id    Cont_id    region
G_1            c_1           Hongkong
G_1            c_2           Malaysia

from example above you see that for the same group_id G_1 there are two contracts c_1 and c_2 and they are both from different regions - hongkong and malaysia - I need to get results from a table/tables that has more than 1 contract assigned to same group_id and that the regions of C1 and c2 are different -
can you guys let me know how to get this information
Comment
Watch Question

Author

Commented:
From the table layout I have group id and contracts in one table and contract id and regions in another table - they both are joined by contract_id
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
please provide sample data for each source table
and expected results

Author

Commented:
group_id table -

group_id     contract_id

G_1           c_1
G_1           c_2
G_2           c_3
G_3           c_4
G_3           c_5
G_3           c_6
....

geo regional Contracts table

Contract_id Geo Regions
c_1               Hongkong
c_2               Malaysia
c_3               Singapore
c_4               India
c_5              India
c_6              India
....
query joining the table that has more than one contract for a group id and then the geo regions for those contracts should be different
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
what should the results be?

Author

Commented:
sorry the expected results should be the group id and contracts that have different geo regions - in my example above only G_1 qualifies the conditions, G_2 does not have more than one contract_id and G_3 has more than one contract but regions are all the same.

Group_id  Contract_id    Regions

G_1          c_1                  Hongkong
G_1          c_2                  Malaysia
CERTIFIED EXPERT

Commented:
select region,min(r.contract_id),g.group_id
  from region r,grupo g
 where g.contract_id=r.contract_id
   and g.group_id in (select g.group_id
                       from grupo g,region c
                      where g.contract_id=c.contract_id
                   group by g.group_id having count(*) >1)
group by region,group_id
having count(*)=1

Author

Commented:
Hi jcgd

first I ran your query and it did not give 1 row as result which is what I expect.

Secondly I need to understand your query having min(r.contract_id) in the select - this only will give one row and at the end having count(*) = 1 also confuses me as to what we are doing on regions to get the rows that are different -

can you explain -

would appreciate your reply as I have to get this done by today

Author

Commented:
any posting on this - would appreciate reply

Author

Commented:
hmm.. increasing the point to 300 to get attention
CERTIFIED EXPERT

Commented:
in sql restriction HAVING COUNT (*) = 1 indicates that only the rows that have taken a single row, therefore, min or max expression has no effect and is only for the "group by" not to fail.

Author

Commented:
" 1 indicates that only the rows that have taken a single row," - not clear - can you please elaborate -
CERTIFIED EXPERT

Commented:
yes, HAVING COUNT (*) = 1 takes only groups with a single row.
Commented:
could you try below query:-

select g.group_id, g.contract_id, r.regions
from group g
inner join region r on g.contract_id = r.contract_id
Where exists (
      select 1 from group a
      inner join region b on a.contract_id = b.contract_id      
      Where a.group_id = g.group_id
      having count(distinct b.regions) > 1
)

Author

Commented:
I tried but got duplicate lines for that one result I was looking for - I added a distinct to the select statement and I got that row - dont know

1. why we need ANSI sql to accomplish the result - I have not used ANSI a lot - some light on this will help
2. is it ok to add that distinct to get the result that I want - not knowing how ANSI joins work I think this alludes to my first question.

Commented:
hi Mahjag

1. ANSI join make the query more readable. In the operation side, no different when you specify the join in your where condition

2. I believe for that below scenario exists, so you get the same result.

group_id     contract_id

G_1           c_1
G_1           c_2
G_1           c_2

geo regional Contracts table

Contract_id Geo Regions
c_1               Hongkong
c_2               Malaysia

That's why the produced result will be
G_1    c_1 HongKong
G_1    c_2 Malaysia
G_1    c_2 Malaysia

Explore More ContentExplore courses, solutions, and other research materials related to this topic.