Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PL/SQL Group by

Posted on 2016-08-11
2
Medium Priority
?
67 Views
Last Modified: 2016-08-11
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
0
Comment
Question by:Allen Pitts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41752225
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

0
 

Author Closing Comment

by:Allen Pitts
ID: 41752837
Well done. Thanks.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

715 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