Sql Query Help

Hi all,

I have table with the following columns and data:

sequence_id organization_id
------------ -------------
50 356
50 356
51 356
52 356
52 356
52 357


Here is my query, which distinct the result group by. (fine up to this point)

select dist_sequence_id,
destination_organization_id
from po_req_dist_interface_all
group by dist_sequence_id,
destination_organization_id

sequence_id organization_id
------------ -------------
50 356
51 356
52 356
52 357

Now what I really need to see is, the count of the sequnce_id that appear more than one (based on the above result) and what are those.

Some kind of inner and outer query can resolve the requirement. The above query is fine, but how I can extract the result that I need at the end?

Thanks in advance.

I'm not exactly sure what you are looking for but here are two options.

Code:

---

SELECT MAX(dist_sequence_id) dist_sequence_id,
      destination_organization_id
  FROM po_req_dist_interface_all
 GROUP BY destination_organization_id
 HAVNG COUNT(*) > 1;

---

Code:

---

SELECT COUNT(*) FROM (
SELECT destination_organization_id
  FROM po_req_dist_interface_all
 GROUP BY destination_organization_id
 HAVNG COUNT(*) > 1);

---

Hi thanks for the reply, but non of the meeting with my output.

Main data store into table:
sequence_id organization_id
------------ -------------
50 356
50 356
51 356
52 356
52 356
52 357

Below is my query which group by and distinct the result, now from this out put need to know the count of the sequnce_id that appear more than one and what are those seq ids.

select dist_sequence_id,
destination_organization_id
from po_req_dist_interface_all
group by dist_sequence_id,
destination_organization_id

sequence_id organization_id
------------ -------------
50 356
51 356
52 356
52 357

The output I am looking is :
sequence_id organization_id
------------ -------------
52 2

Thanks.

Code:

---

SELECT dist_squence_id, count(*) FROM
(select dist_sequence_id,
 destination_organization_id
 from po_req_dist_interface_all
 group by dist_sequence_id,
 destination_organization_id)
HAVING count(*) > 1

---

Hi Dapi,

I Just entered the below line before the last line,
group by dist_sequence_id

and it works perfect. Thanks for your help to resolve this issue.

Appreciate.

Oops :)