Solved

Using grouping and sub groups in oracle query

Posted on 2012-03-13
15
329 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
0
Comment
Question by:mahjag
  • 8
  • 3
  • 2
  • +1
15 Comments
 

Author Comment

by:mahjag
ID: 37716018
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37716059
please provide sample data for each source table
and expected results
0
 

Author Comment

by:mahjag
ID: 37716296
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37716319
what should the results be?
0
 

Author Comment

by:mahjag
ID: 37716397
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
0
 
LVL 3

Expert Comment

by:jcgd
ID: 37716718
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
0
 

Author Comment

by:mahjag
ID: 37716789
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mahjag
ID: 37717045
any posting on this - would appreciate reply
0
 

Author Comment

by:mahjag
ID: 37717272
hmm.. increasing the point to 300 to get attention
0
 
LVL 3

Expert Comment

by:jcgd
ID: 37717529
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.
0
 

Author Comment

by:mahjag
ID: 37717712
" 1 indicates that only the rows that have taken a single row," - not clear - can you please elaborate -
0
 
LVL 3

Expert Comment

by:jcgd
ID: 37717737
yes, HAVING COUNT (*) = 1 takes only groups with a single row.
0
 
LVL 11

Accepted Solution

by:
yuching earned 300 total points
ID: 37718673
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
)
0
 

Author Comment

by:mahjag
ID: 37720569
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.
0
 
LVL 11

Expert Comment

by:yuching
ID: 37723513
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wrap Oraccle SQL*Plus executable Command 4 66
Schema creation in Oracle12c 6 39
Read only access to a Procedure in oracle? 4 45
data lookup in Oracle - need suggestions 55 102
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now