Count a Count Query

Analytics
Enthusiast

Count a Count Query

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. 

1 REPLY
Teradata Employee

Re: Count a Count Query

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;