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
 ;
Allen PittsBusiness analystAsked:
Who is Participating?
 
SujithConnect With a Mentor Data 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
 
johnsoneConnect With a Mentor Senior 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
 
awking00Commented:
Can you provide some sample relevant data (i.e. just the pertinent fields) for the two tables and the expected results?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.