aggr on derived field

Database
N/A

aggr on derived field

ACCOUNT_ID PRODUCT_ID COUNTRY_ID RX_FORMULARY_STATUS
3,022 1,161 67 Unrestricted
3,022 1,161 124 Unrestricted
3,022 1,161 168 Unrestricted
93,799 403 67 Approved
93,799 403 124 Approved
93,799 403 168 Approved
396,009 689 67 Restricted
396,009 689 124 Restricted
396,009 689 168 Restricted
499,107 135 67 Not Applicable
499,107 135 124 Not Applicable
499,107 135 168 Not Applicable
499,107 403 67 In Progress
499,107 403 124 In Progress
499,107 403 168 In Progress
540,293 501 67 In Progress

This is the logic for derived filed

CASE
WHEN A. RX_FORMULARY_STATUS IN ('Approved', 'Unrestricted')
THEN 'Positive'
WHEN A.RX_FORMULARY_STATUS IN ('Blacklist', 'Rejected')
THEN 'Negative'
WHEN A.RX_FORMULARY_STATUS IN ('NA','None', 'In Progress','Regional Committee', 'Restricted','Not Applicable')
THEN 'Neutral'

END AS CURR_CLUSTER

 

now i want to count of positive and negative and netural combination of accountid and productid

 

Kindly suggest the solution

3 REPLIES
Teradata Employee

Re: aggr on derived field

Not checked for reserved words:

 

SELECT ACCOUNT_ID, PRODUCT_ID, SUM(POSITIVE), SUM(NEGATIVE), SUM(NEUTRAL)

FROM (

   SELECT

   (CASE <your business rules>) AS CURR_CLUSTER,

   (CASE CURR_CLUSTER WHEN 'Positive' THEN 1 ELSE 0 END) AS POSITIVE,

   (CASE CURR_CLUSTER WHEN 'Negative' THEN 1 ELSE 0 END) AS NEGATIVE,

   (CASE CURR_CLUSTER WHEN 'Neutral' THEN 1 ELSE 0 END) AS NEUTRAL

   FROM <source table>

) TBLA

GROUP BY 1, 2

;

Re: aggr on derived field

I Hope this would help select account_id,product_id,sum(rx.positive),sum(rx.negative) from(select account_id,product_id, case when rx_formula in ('approved','unrestricted') then 1 else 0 end as positive, case when rx_formula not in ('Blacklist', 'Rejected') then 1 else 0 end as negative from status)rx group by account_id,product_id ;
Teradata Employee

Re: aggr on derived field

Note that it is also fine to put a case expression within an aggregation.

 

select acct, prod, sum( case expr1 ), sum( case expr2 ) from table group by acct, prod;