【Question】

I have one table called ‘activity_data’, which contains the following fields: id (int(11)) , sports_name (varchar(20)), activity_date (datetime)

Here’s information of activity_data table:

 

id sports_name activity_date

 

1 Cricket 2015-05-12

 

2 Football 2015-05-12

 

3 Cricket 2015-06-10

 

4 Basketball 2015-06-08

 

5 Khokho 2015-06-02

 

6 Kubbdi 2015-06-04

 

7 Cricket 2015-05-12

 

8 Kubbdi 2015-05-12

 

9 Cricket 2015-06-09

 

10 Football 2015-06-03

 

Now I’m executing the following queries:

 

SELECT GROUP\_CONCAT(id) id ,GROUP\_CONCAT(DISTINCT(sports\_name)) sports\_name,activity_date

FROM filter\_activity\_data

WHERE activity_date BETWEEN  '2015-05-12'  AND  '2015-06-04' 

GROUP  BY activity_date

ORDER  BY activity_date ASC

 

Output is like this:

 

id  sports_name  activity_date

 

1,2,7,8  Cricket,Football,Kubbdi 2015-05-12

 

5  Khokho 2015-06-02

 

10 Football 2015-06-03

 

6  Kubbdi 2015-06-04

 

… …

 

I want to get total count of each GROUP_CONCAT sports_name. Expected output is like this:

 

id  sports_name activity_date

 

2,1,1  Cricket,Football,Kubbdi 2015-05-12

 

1  Khokho 2015-06-02

 

1  Football 2015-06-03

 

1  Kubbdi 2015-06-04

 

… …

 

【Answer】

SQL solution:

select GROUP_CONCAT(t.name1) as name2 , GROUP_CONCAT(t.count) as counts , t.activity_date from

 

(SELECT GROUP\_CONCAT(id) id ,GROUP\_CONCAT(DISTINCT(sports_name)) as name1 , count(*) as count , sports\_name,activity\_date

 FROM activity_data

 WHERE activity_date BETWEEN  '2015-05-12'  AND  '2015-06-04'

 GROUP  BY activity\_date, sports\_name

 ORDER  BY activity_date ASC) as t

 

group  by t.activity_date

 

There’s a more concise solution with the same algorithm. It is the SPL (Structure Process Language) solution, as shown below:

	A
1	$select * from activity_data
2	=A1.groups(activity_date,sports_name;count(~):c)
3	=A2.group@o(activity_date).new(~.(c).concat@c():id,~.(sports_name).concat@c():sports_name,activity_date)

A1: Retrieve data in SQL;

A2: Perform grouping and count over the records;

A3: Concatenate records of all groups. Here’s the final result: