PL/SQL Display how many times one field value is a listed for another field value

Hello Expert,

In our data schema Protocols can have one or more Components
in a CCM_PROTOCOL_COMPONENTS table with fields:

PROTOCOL_COMPONENT_ID
PROTOCOL_ID
LAST_UPDATE_USER
LAST_UPDATE_DATE
IS_DISABLED
COMPONENT_ID
CREATION_USER
CREATION_DATE
OWNER_ID
OWNER_TYPE_CODE
PUBLISH_STATUS_CODE
LAST_PUBLISH_DATE
EXAM_ID

Have  a query  that groups the Components by COMPONENT_IDs ,
display how many times a Component is listed and
joins the Component_ID to the Component table to get the
Component_Name, copied herewith below.

Is there  a way to group by Protocols and list the Components
that are associated with each Protocol?

Thanks.

Allen in Dallas



---Component Group By Query---
 select
 GBY.COMPONENT_ID,
 COMPONENT_NAME,
 Num
 from
 (SELECT COMPONENT_id, COUNT(*) as Num
FROM CCM.CCM_COMPONENT_PRICING CMP
 group by COMPONENT_ID
) GBY
  join  CCM_COMPONENT CMP on CMP.COMPONENT_ID = GBY.COMPONENT_ID
  order by gby.Component_ID desc
 ;
LVL 1
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Is this what you are looking for -

SELECT  GBY.PROTOCOL_ID, GBY.COMPONENT_id, COMPONENT_NAME, COUNT(*) as Num
FROM    CCM.CCM_COMPONENT_PRICING GBY INNER JOIN CCM_COMPONENT CMP on CMP.COMPONENT_ID = GBY.COMPONENT_ID
GROUP BY GBY.PROTOCOL_ID, GBY.COMPONENT_ID, COMPONENT_NAME
ORDER BY GBY.PROTOCOL_ID, gby.Component_ID DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Just a word on your question title.

This is not a PL/SQL question.  This is a SQL question.  PL/SQL is Oracle's procedural language used for packages, functions, procedures, triggers, etc.  This question has nothing to do with that and is a straight SQL question.

Including PL/SQL in your question title will get some people that could help you to not even look at your question.  There are people that are really good at SQL and not PL/SQL that will skip the question because it isn't in their area of expertise.  You will also get people that don't have the time to decipher a PL/SQL question to skip it (I do this when I don't have the time).

Proper terminology gets the right experts to look at your questions.
0
awking00Information Technology SpecialistCommented:
Can you provide some sample relevant data (i.e. just the pertinent fields) for the two tables and the expected results?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Allen PittsBusiness analystAuthor Commented:
Sujith80: Not exactly what needed but close enough that it could be tweaked

Johnsone: More than once in discussion  of a query it has been said "SQL is SQL". But there are differences between T-SQL and PL/SQL.
Both languages do not use Top(100). Both languages do not recognize Row Num. One  can not convert the code of T-SQL to PL-SQL as they have different features, syntax, they differ in a way how they handle their variables, stored procedures, and built-in function. T-SQL is simpler and easier whereas, PL-SQL is complex, but it is potentially more powerful.

In T-SQL the schema can be manipulated anywhere, anytime, with little or no fuss using CREATE, ALTER, and DROP statements.

However, Oracle doesn't allow DDL in stored procedures so you have jump through hoops. In order to perform a DDL function, you need to use EXECUTE IMMEDIATE, which is used to execute dynamic SQL statements.

The two languages are different in many syntax elements.
0
johnsoneSenior Oracle DBACommented:
I'm not talking about the different between TransactSQL and PL/SQL.  Two different but similar products.  Oracle SQL and Oracle PL/SQL are 2 different things.

While Microsoft, Oracle, Sybase, CA, etc. have all implemented and extended SQL in their own way, that doesn't make a procedural language the same as an interactive language.  Just like different compilers of the same language work differently, some compile the code just fine, but the same legal code won't compile on a compiler from a different vendor.

I'm just trying to help you get the right and/or more people to look at your question.  You posted the question in an Oracle space, so people are pretty sure you are looking at Oracle.
0
Allen PittsBusiness analystAuthor Commented:
Thanks Johnsone for the comment. Will broaden topics selection.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.