CODEWITHSUNDEEP
sqloracle-databaseoracle-apex-5
Mcateer
Mcateer

Reputation: 23

How to count unique occurences of string in table for separate records in apex 5

I am trying to automatically count the unique occurrences of a string saved in the table. Currently I have a count of a string but only when a user selects the string and it gives every record the same count value.

For example

Below is a image of my current table:

Current Table

From the image you can see that there is a Requirement column and a count column. I have got it to the point were when the user would select a requirement record (each requirement record has a link) it would insert the requirement text into a requirement item called 'P33_REQUIREMENT' so the count can have a value to compare to.

This is the SQL that I have at current: 

SELECT (SELECT COUNT(*) 
        FROM DIA_ASSOCIATED_QMS_DOCUMENTS 
        WHERE REQUIREMENT = :P33_REQUIREMENT 
        group by REQUIREMENT 
       ) AS COUNT, 
       DPD.DIA_SELECTED, 
       DPD.Q_NUMBER_SELECTED, 
       DPD.SECTION_SELECTED, 
       DPD.ASSIGNED_TO_PERSON, 
       DAQD.REFERENCE, 
       DAQD.REQUIREMENT, 
       DAQD.PROGRESS, 
       DAQD.ACTION_DUE_DATE, 
       DAQD.COMPLETION_DATE, 
       DAQD.DIA_REF,  
       DA.DIA, 
       DA.ORG_RISK_SCORE 
FROM DIA_PROPOSED_DETAIL DPD, 
     DIA_ASSOCIATED_QMS_DOCUMENTS DAQD,   
     DIA_ASSESSMENTS DA
WHERE DPD.DIA_SELECTED = DAQD.DIA_REF 
  AND DPD.DIA_SELECTED = DA.DIA

This is the sql used to make the table in the image.

This issue with this is, it is giving every record the same count when the user selects a requirement value. I can kind of fix this by also adding in AND DIA_SELECTED = :P33_DIA into the where clause of the count. DIA_SELECTED being the first column in the table and :P33_DIA being the item that stores the DIA ref number relating to the record chosen.

The output of this looks like:

Table with extra where clause

As you can see there is only one count. Still doesn't fix the entire issue but a bit better.

So to sum up is there a way to have the count, count the occurrences individually and insert them in the requirements that are the same. So if there are three tests like in the images there would be a '3' in the count column where requirement = 'test', and if there is one record with 'test the system' there would be a '1' in the count column.

Also for more context I wont know what the user will input into the requirement so I can't compare to pre-determined strings.

I'm new to stack overflow I am hoping I have explained enough and its not too confusing.

Upvotes: 2

Views: 114

Answers (1)

Koen
Koen

Reputation: 734

The following extract:

SELECT (SELECT COUNT(*) 
        FROM DIA_ASSOCIATED_QMS_DOCUMENTS 
        WHERE REQUIREMENT = :P33_REQUIREMENT group by REQUIREMENT ) AS COUNT

Could be replaced by

SELECT (SELECT COUNT(*) 
        FROM DIA_ASSOCIATED_QMS_DOCUMENTS 
        WHERE REQUIREMENT = DAQD.REQUIREMENT ) AS COUNT

Which would give you - for each line, the number of requirements that are identical. I'm not completely certain it is what you are after, but if it isn't, it should give you some ideas on how to progress (or allow you to indicate where I failed to understand your request)

Upvotes: 2

Related Questions