MySQL query to show data separated by comma with group by different columns

Question

I have a MySQL table where I have stored all of users searches. So the table looks something like this

CREATE TABLE `users_search_activity` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `search_keywords` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users_search_activity`
--

INSERT INTO `users_search_activity` (`ID`, `user_id`, `country_id`, `search_keywords`, `date`) VALUES
(1, 132, 2, 'xavie', '2021-07-13 08:20:37'),
(2, 132, 6, 'xavier', '2021-07-13 08:21:38'),
(3, 132, 5, 'xavier ins', '2021-07-13 08:21:39'),
(4, 132, 4, 'xavier ins', '2021-07-13 08:21:39'),
(5, 131, 9, 'xavier ins',  '2021-07-13 08:22:12'),
(6, 132, 7, 'xavier ins', '2021-07-13 08:22:25'),
(7, 132, 8, 'xavier ins', '2021-07-13 09:24:43'),
(8, 132, 6, 'xavier ins', '2021-07-13 09:24:45'),
(9, 132, 4, 'xavier insa', '2021-07-13 09:24:47'),
(10, 131, 5, 'ins', '2021-07-13 09:24:54'),
(11, 132, 3, 'ins', '2021-07-13 09:24:54'),
(12, 132, 2, 'ins', '2021-07-13 09:24:58'),
(13, 132, 9, 'ins', '2021-07-13 09:24:59'),
(14, 132, 0, 'ins', '2021-07-13 09:25:00'),
(15, 132, 0, 'ins', '2021-07-13 09:25:02'),
(16, 132, 0, 'inst', '2021-07-13 09:58:20'),
(17, 132, 0, 'inst', '2021-07-04 09:58:25'),
(18, 132, 0, 'inst', '2021-07-07 09:58:25'),
(19, 132, 0, 'inst', '2021-07-11 09:58:26'),
(20, 1, 12, 'University Business Academy in Novi Sad', '2021-07-14 10:16:33');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users_search_activity`
--
ALTER TABLE `users_search_activity`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users_search_activity`
--
ALTER TABLE `users_search_activity`
  MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
COMMIT;

Now I want to make some query from where I can get the data group by country_id and date. So for that I have made my query like this

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY country_id, DATE(date) ORDER BY ID) rn, COUNT(*) OVER (PARTITION BY country_id, DATE(date)) cnt FROM users_search_activity ) SELECT ID, cnt AS count, search_keywords, user_id, country_id, DATE(date) as date FROM cte WHERE rn = 1;

Here its working fine except the search_keywords. It is only showing single search_keywords. I want to show them all one by one separated by comma for the given date and country_id.

So can someone tell me how to do that? Any help or suggestions would be really appreciable. Thanks,

The output should be something like this

count  user_id  country_id  date         search_keywords
 1     132       4          2021-07-13  xavier ins, xavier insa

Something like this count user_id country_id date search_keywords 1 132 4 2021-07-13 xavier ins, xavier insa — NewUser
– NewUser, Commented Jul 27, 2021 at 8:02
@Sam020 by using GROUP_CONCAT it is getting all the search_keywords in a single row. But I want them to show based on the country_id and date. — NewUser
– NewUser, Commented Jul 27, 2021 at 8:18
... based on the country_id and date but you also have user_id in the results. — forpas
– forpas, Commented Jul 27, 2021 at 8:19

nayi224 · Accepted Answer · 2021-08-03 09:57:29Z

1

you can use group_concat

WITH cte AS ( 
SELECT *,DATE(date),ROW_NUMBER() OVER (PARTITION BY country_id, DATE(date) ORDER BY ID) rn, 
        COUNT(*) OVER (PARTITION BY country_id, DATE(date)) cnt 
  FROM users_search_activity 
  ) 
,tab2 as (
select t1.country_id,
       date(date) dat,
       group_concat(t1.search_keywords)
  from cte t1
 group by t1.country_id,
       date(date)
)
 SELECT *
  FROM cte t1,
       tab2 t2
 WHERE t1.rn = 1
   and t1.country_id = t2.country_id
   and DATE(t1.date) = t2.dat
 ;

update at 2021/8/3

5.7 also has group_concat,you just need to deal with row_number.

select t1.country_id,
       date(date) dat,
       group_concat(t1.search_keywords),
       max(case when t1.row_number = 1 then t1.user_id else null end) user_id
  from (
select t1.*,
       @rn := case when @temp1 is null then 1
                   when @temp1 = t1.country_id and @temp2 = DATE(date) then 0
                   else 1
              end row_number,
       @temp1:= t1.country_id,
       @temp2:= DATE(date)
  from users_search_activity t1,(select @rn:=0, @temp1:='', @temp2:='') t2
 order by country_id, DATE(date), t1.id
) t1
 group by t1.country_id,
       date(date)
;

edited Aug 3, 2021 at 9:57

answered Jul 27, 2021 at 8:21

nayi224

5652 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NewUser Over a year ago

Any solution for lower version of MySQL?

nayi224 Over a year ago

Which version are you using？

NewUser Over a year ago

I have 5.7 version.

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-07-27 08:30:12Z

First of all thanks for providing such detail information in text format. Your question deserves upvote for that.

You don't need any ranking function like row_number() or cte. Simple group by with group_concat() is enough.

Query:

SELECT COUNT(*) OVER (PARTITION BY country_id, DATE(date)) AS count, 
   user_id, country_id, DATE(date) as date, group_concat(search_keywords) FROM users_search_activity
   group by user_id,country_id,DATE(date)

Output: |count | user_id | country_id | date | group_concat(search_keywords)
|----: | ------: | ---------: | :--------- | :-------------------------------------- | 1 | 132 | 0 | 2021-07-04 | inst
| 1 | 132 | 0 | 2021-07-07 | inst
| 1 | 132 | 0 | 2021-07-11 | inst
| 1 | 132 | 0 | 2021-07-13 | inst,ins,ins
| 1 | 132 | 2 | 2021-07-13 | xavie,ins
| 1 | 132 | 3 | 2021-07-13 | ins
| 1 | 132 | 4 | 2021-07-13 | xavier insa,xavier ins
| 2 | 131 | 5 | 2021-07-13 | ins
| 2 | 132 | 5 | 2021-07-13 | xavier ins
| 1 | 132 | 6 | 2021-07-13 | xavier ins,xavier
| 1 | 132 | 7 | 2021-07-13 | xavier ins
| 1 | 132 | 8 | 2021-07-13 | xavier ins
| 2 | 131 | 9 | 2021-07-13 | xavier ins
| 2 | 132 | 9 | 2021-07-13 | ins
| 1 | 1 | 12 | 2021-07-14 | University Business Academy in Novi Sad

db<>fiddle here

This is only working with MySQL 8+ . Is there any solution for lower version?

Collectives™ on Stack Overflow

MySQL query to show data separated by comma with group by different columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related