cancel
Showing results for 
Search instead for 
Did you mean: 

Distinct Counting records

former_member424792
Participant
0 Kudos

I have a report column that is counting the number of records in the database under a certain agreement name, if the matter has this agreement name it is being counted in this column. However when I try to sum the column my summary field is counting duplicates of random agreement names. How do I get the sum for this column without the duplicate information?

(Say for the cell that says 33, some of those are pulling more than once into the view due to different cross reference information, the 33 is correct but when I total it I am getting a count of those 33 plus 4  of the duplication's)  I hope I am explaining this properly.

Thank you in advance,

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Matt,

How do you 'count' these agreements?

Is that using a formula? Could you post the formula as well?

-Abhilash

former_member424792
Participant
0 Kudos

I have done a variety of thing, a simple Sum({Groupname}). Also go to insert Summary and select the field I want to sum. As for the actual column I created a table that is doing a Distinct count in the database for that field.

former_member424792
Participant
0 Kudos

This is how I am creating the table for the column:

CREATE OR REPLACE FORCE VIEW "GOOGLE_tgg"."COUNT_DD"

(

   "LEGACY_FILE_M",

   "AGMT_NAME",

   "NUM_FILES"

)

AS

   SELECT legacy_file_m, agmt_name, COUNT (arrg_key) NUM_FILES

     FROM ALL_AGREEMENTS

    WHERE legacy_file_m = '123456.001' AND SUBJ_CODE = 'LSE'

   GROUP BY legacy_file_m, agmt_name

Former Member
0 Kudos

Hello Matt,

Could you please try counting distinct records with below SQL?

CREATE OR REPLACE FORCE VIEW "GOOGLE_tgg"."COUNT_DD"

(

   "LEGACY_FILE_M",

   "AGMT_NAME",

   "NUM_FILES"

)

AS

   SELECT legacy_file_m, agmt_name, COUNT (Distinct arrg_key) NUM_FILES

     FROM ALL_AGREEMENTS

    WHERE legacy_file_m = '123456.001' AND SUBJ_CODE = 'LSE'

   GROUP BY legacy_file_m, agmt_name;

Regards

Niraj