All Forums Database
lahiri.arijit 3 posts Joined 05/13
18 Jul 2014
Concatenating texts while summarizing data

Hi, I have a dataset of the following form:

 

mem ofr sales

1       Z      3

1       A      5

2       B      4

2       C      2

 

I want to summarize the data for each member which will have total sales and concatenated values of offers they have received.

The output should look like:

 

mem   ofr    sales

1        Z|A     8

2        B|C     6

 

lahiri.arijit 3 posts Joined 05/13
19 Jul 2014

Hi experts, can anybody solve my above problem. Let me know if you need any further clarification.
Thanks,
Arijit

Raja_KT 1246 posts Joined 07/09
19 Jul 2014

Which td version you are in?
select mem, tdstats.udfconcat(ofr),sum(sales) from your_table group by 1;
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

lahiri.arijit 3 posts Joined 05/13
20 Jul 2014

Thanks Raja. I am using TD 13.11. It is giving error saying column tdstats not found in my_table.
 

Raja_KT 1246 posts Joined 07/09
20 Jul 2014

Whew!!!  you have to get one else you can do something thus.
 
select id,max(case when rn=1 then ofr else '' end)||'|'||max(case when rn=2 then ofr else '' end),sale from 
(select mem,ofr,sum(sales) over(partition by id) sale, row_number() over(partition by id order by id) rn from test) t1 group by 1,3 order by 1

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.