PL/SQL Group by

Allen Pitts
Allen Pitts used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you have to count and not to Sum

Select owner_ID, count(protocol_id) as CNT
from ccm_protocol
group by owner_ID
--having SUM(protocol_id) between 1 and 601000
order by CNT desc

Open in new window

Allen PittsBusiness analyst

Author

Commented:
Well done. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial