MySQL query to show data separated by comma with group on lower version of MySQL

Question

I have a table 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

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)

This is working fine MySQL 8+ but can someone tell me how to make this query so that it would work with older version of MySQL also?

Any help and suggestions would be really appreciable.

Update Here is the error what I am getting when trying the query with MySQL 5.7

19 errors were found during analysis.

An alias was previously found. (near "count" at position 62)
An alias was expected. (near " " at position 61)
Unrecognized keyword. (near "count" at position 62)
Unexpected token. (near "," at position 67)
Unexpected token. (near "user_id" at position 74)
Unexpected token. (near "," at position 81)
Unexpected token. (near "country_id" at position 83)
Unexpected token. (near "," at position 93)
Unrecognized keyword. (near "DATE" at position 95)
Unexpected token. (near "(" at position 99)
Unrecognized keyword. (near "date" at position 100)
Unexpected token. (near ")" at position 104)
Unrecognized keyword. (near "as" at position 106)
Unrecognized keyword. (near "date" at position 109)
Unexpected token. (near "," at position 113)
Unrecognized keyword. (near "group_concat" at position 115)
Unexpected token. (near "(" at position 127)
Unexpected token. (near "search_keywords" at position 128)
Unexpected token. (near ")" at position 143)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY country_id, DATE(date)) AS count,
   user_id, country_id, DATE(d' at line 1

But with MySQL 8.1 I am not getting any errors.

Answer 1

Use correlated subquery:

SELECT ( SELECT COUNT(*)
         FROM users_search_activity usa_sq
         WHERE (usa.country_id, DATE(usa.date)) = (usa_sq.country_id, DATE(usa_sq.date))
         ) AS count, 
       user_id, 
       country_id, 
       DATE(date) as date, 
       GROUP_CONCAT(search_keywords) 
FROM users_search_activity usa
GROUP BY 1,2,3,4
ORDER BY 2,3,4

https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.7&fiddle=9a49f48ca0663b3dd050753f63ca6dd1