CODEWITHSUNDEEP
sqlteradata
user2653353
user2653353

Reputation: 105

Teradata -Duplicate entries elimination while aggregating

I am trying to eliminate the duplicates when aggregating the data.

CREATE  TABLE abc
( dt DATE FORMAT 'YYYY-MM-DD'
,acct NUMBER
,chk_lmt VARCHAR(10)
,baln_lmt VARCHAR(10)
,wk DATE FORMAT 'YYYY-MM-DD'
) ;
    INSERT  INTO abc
('2018-03-19' , 1001, 'under 1000' , 'under 500', '2018-03-23');

INSERT  INTO abc
('2018-03-20' , 1001, 'under 1000' , 'under 500', '2018-03-23');

INSERT  INTO abc
('2018-03-20' , 1002, 'under 1000' , 'under 500', '2018-03-23');

SELECT  dt,wk,'chk_lmt_chk' AS TYPEs, 
SUM ( CASE  WHEN chk_lmt = 'under 1000' THEN 1 ELSE 0 END   ) AS counts
  FROM abc
  GROUP BY 1,2,3;

    dt  wk  TYPEs   counts
19/03/2018  23/03/2018  chk_lmt_chk 1
19/03/2018  23/03/2018  chk_lmt_chk 0
20/03/2018  23/03/2018  chk_lmt_chk 2

For the dt 20/03/2018, the count should be 1 since the acct is already captured in the 19/03/2018.

Any ideas?

Thanks

Upvotes: 1

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

THIS ANSWERS THE ORIGINAL QUESTION.

If you want the account to be counted only once, the first time, use row_number():

SELECT dt, wk, 'chk_lmt_chk' AS TYPEs, 
       SUM(CASE WHEN chk_lmt = 'under 1000' THEN 1 ELSE 0 END) AS counts
FROM (SELECT abc.*,
             ROW_NUMBER() OVER (PARTITION BY acct ORDER BY dt) as seqnum
      FROM ptemp.abc
     ) abc
WHERE seqnum = 1
GROUP BY 1, 2, 3;

Upvotes: 1

Related Questions