-
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
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|