avoiding double count in load group by

[AJKhan]

Hi,

I have a following load

Data:

load
customer_id,
customer_prd,
date

from data.csv

Data_final:

load

count(distinct customer_id) as No_of Cust,
customer_prd,
date

resident Data
group by (customer_prod,date)

drop table Data;

however problem is, i am getting double/triple counts of the customers as unique customer_id exists across multiple customer_prd . Solution i am looking at is to grab the unique customer counts only across the data sets. Is this possible? thanks 

[sasiparupudi1 · Accepted solution]

May be like this?

Data_final:

load

date,

count(distinct customer_id) as No_of Cust,
Concat(customer_prd,',') As customer_prd,
 

resident Data
group by  date