Hello, I currently have a query (below). This code gives the correct results, about 94,000 records. The a.ID column contains every single ID number which is in the D_Table that has at least 2 different CLM_ID's. The second column tells me exactly how many CLM_ID's each a.ID has. The third column ccc.CCode is related to a differnt table and gives me the most current value per that a.ID.
SELECT DISTINCT a.ID, COUNT(DISTINCT a.CLM_ID) #OFCLAIMS, ccc.CCode FROM ODatabase.D_Table a LEFT JOIN ( SELECT DISTINCT ID, EVT_DT, CCode FROM CB_Database.CB_Table QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EVT_DT DESC) = 1 ) ccc ON a.ID = ccc.ID WHERE END_DT = '9999-12-31' AND a.RSLV_TS IS NOT NULL GROUP BY a.ID, ccc.CCode HAVING #OFCLAIMS > 1
What I am trying to do is create a count query to count up the ~94,000K records (IDs) and group them for the ccc.CCode value found.
DISTINCT is redundant since you already have GROUP BY.
Seems like all you need is something like
SELECT CCode, COUNT(*) as #IDs, SUM(#OFCLAIMS)
FROM ( your original query ) as q1;