Allen Pitts
asked on
PL/SQL Group by
Hello experts,
So it is needed to know which Owners have the most Protocols
and how many.
A query was crafted
Select owner_ID, SUM(protocol_id) as CNT
from ccm_protocol
group by owner_ID
--having SUM(protocol_id) between 1 and 601000
order by CNT desc
which returned
Owner_ID CNT
258490 2631271323
306118 2435400026
296588 2261418059
207643 1916887537
212733 1866463289
202729 1729376813
430247 1690954678
213027 1670415116
213978 1601271602
257757 1593747213
Which blew my mind cause I think this says
that Owner_ID 258490 has 261 million protocols.
So the query was input
Select Owner_id, protocol_id
from ccm_protocol
where owner_id = 258490
which returned 1872 rows.
So there is something wrong with the GROUP BY query.
How can I get a list of OWNER_IDs with the number of
Protocols for each OWNER_ID?
Thanks.
Allen in Dallas
So it is needed to know which Owners have the most Protocols
and how many.
A query was crafted
Select owner_ID, SUM(protocol_id) as CNT
from ccm_protocol
group by owner_ID
--having SUM(protocol_id) between 1 and 601000
order by CNT desc
which returned
Owner_ID CNT
258490 2631271323
306118 2435400026
296588 2261418059
207643 1916887537
212733 1866463289
202729 1729376813
430247 1690954678
213027 1670415116
213978 1601271602
257757 1593747213
Which blew my mind cause I think this says
that Owner_ID 258490 has 261 million protocols.
So the query was input
Select Owner_id, protocol_id
from ccm_protocol
where owner_id = 258490
which returned 1872 rows.
So there is something wrong with the GROUP BY query.
How can I get a list of OWNER_IDs with the number of
Protocols for each OWNER_ID?
Thanks.
Allen in Dallas
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER