MAX value in a group and group name

Database
Enthusiast

MAX value in a group and group name

I have that I have written that:

SyntaxEditor Code Snippet

SELECT 
     C.CLNT_NO    ,Count(DISTINCT(A.COMPANY)) AS COMPANY    ,Count(CLNT_NO) AS NUMBER_TXN    ,Sum(A.AMOUNT) AS TOTAL_SPEND    ,Max(A.AMOUNT) AS MAX_SPEND
FROM TEMP A
        LEFT JOIN DDWV01.DEPOSIT_ACCOUNT B ON (A.AR_ID = B.AR_ID)
        LEFT JOIN DDWV01.CLNT_AR_RELTN_MTH C ON (B.AR_ID = C.AR_ID) AND (B.SNAP_DT = C.SNAP_DT)
        WHERE
        B.SNAP_DT = (SELECT Max (SNAP_DT) FROM  DDWV01.DEPOSIT_ACCOUNT)
        AND B.ACTIVE_INACTV_STTS IN ('A')
        AND PRMRY_CLNT_IND IN ('P')
        AND C.CLNT_NO = '633842356'
        GROUP BY 1
        ORDER BY 1

 

 

 

 Which gives me the following output:

 

  CLNT_NO COMPANY NUMBER_TXN TOTAL_SPEND MAX_SPEND1

12345641279,465.02163.09

 

The second column (company) contains company A, B, C, and D.  I want the last two columns in this output to fetch the company with the largest TOTAL spend and the value of the total spend for that group:

 

  CLNT_NO COMPANY NUMBER_TXN TOTAL_SPEND MAX GROUP MAX SPEND 1

12345641279,465.02A8,000.00

**ASSUMING GROUP A has the most spend out of the 4 groups and spent 8,000.00.

 

I have tried the following code in my select statement but am getting the aggregated error.  

SyntaxEditor Code Snippet

--,(Max(A.AMOUNT) Over (PARTITION BY A.COMPANY) 

 

2 REPLIES
Teradata Employee

Re: MAX value in a group and group name

I think you have to aggregate by Client, Company first; then you can aggregate by Client. E.g.,

SELECT CLNT_NO
	,Count(DISTINCT(COMPANY))
	,Sum(NUMBER_TXN)
	,Sum(TOTAL_SPEND)
	,Last_Value(COMPANY) Over(Partition by CLNT_NO, COMPANY Order By CLNT_NO, COMPANY, TOTAL_SPEND)
	,Max(TOTAL_SPEND) Over(Partition by CLNT_NO, COMPANY)
From (SELECT C.CLNT_NO as CLNT_NO
		,A.COMPANY AS COMPANY
		,Count(CLNT_NO) AS NUMBER_TXN
		,Sum(A.AMOUNT) AS TOTAL_SPEND
	FROM TEMP A
	LEFT JOIN DDWV01.DEPOSIT_ACCOUNT B
	 ON   (A.AR_ID = B.AR_ID)
	LEFT JOIN DDWV01.CLNT_AR_RELTN_MTH C
	 ON   (B.AR_ID = C.AR_ID)
	 AND  (B.SNAP_DT = C.SNAP_DT)
	 WHERE B.SNAP_DT = (SELECT Max(SNAP_DT) FROM DDWV01.DEPOSIT_ACCOUNT)
	 AND  B.ACTIVE_INACTV_STTS IN ('A')
	 AND  PRMRY_CLNT_IND IN ('P')
	GROUP BY 1, 2) Agg
Where C.CLNT_NO = '633842356'
GROUP BY 1
ORDER BY 1

 

Visitor

Re: MAX value in a group and group name

How about just add a qualify by clause in a subquery: 

 

SyntaxEditor Code Snippet

(SELECT a.company compName, a.amount max_sold 
FROM temp a
QUALIFY Sum(amount) Over (PARTITION BY company ) >= (SELECT Max(amount) FROM a))