0

I have a mysql 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;

To get the search keywords separated by comma group by country_id and user_id I have used this query

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

Its working absolutely fine but only with mySQL 8+ version and doesn't work with 5.7. So can someone tell me how to make this query to run with 5.7?

Any help and suggestions would be really appreciable.

FYI: Here is the fiddle.

5
  • 1
    Windows function are first introduced on MySQL 8.0 : dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html Sep 20 '21 at 13:57
  • The above query gives the exact desired result what I want. Only thing is its not working with MySQL 5.7
    – newuser
    Sep 20 '21 at 14:10
  • As Ergest Basha told you, window functions were introduced on MySQL 8. You cannot use COUNT(*) OVER (PARTITION BY country_id) on MySQL 5.7
    – McNets
    Sep 20 '21 at 14:15
  • So is there any alternate function for 5.7 which I can use and get the desired result what I am getting with 8.0?
    – newuser
    Sep 20 '21 at 14:17
  • (Unrelated: Suggest adding DISTINCT in the GROUP_CONCAT(DISTINCT ...).
    – Rick James
    Sep 21 '21 at 0:31
2

Here's what your query returns when I test it on MySQL 8.0:

mysql> SELECT COUNT(*) OVER (PARTITION BY country_id) AS  count, user_id, country_id, DATE(date) as date,  group_concat(search_keywords) as search_keywords   FROM users_search_activity group by country_id;
+-------+---------+------------+------------+-----------------------------------------+
| count | user_id | country_id | date       | search_keywords                         |
+-------+---------+------------+------------+-----------------------------------------+
|     1 |     132 |          0 | 2021-07-11 | inst,inst,inst,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 ins,xavier insa                  |
|     1 |     132 |          5 | 2021-07-13 | xavier ins,ins                          |
|     1 |     132 |          6 | 2021-07-13 | xavier,xavier ins                       |
|     1 |     132 |          7 | 2021-07-13 | xavier ins                              |
|     1 |     132 |          8 | 2021-07-13 | xavier ins                              |
|     1 |     132 |          9 | 2021-07-13 | ins,xavier ins                          |
|     1 |       1 |         12 | 2021-07-14 | University Business Academy in Novi Sad |
+-------+---------+------------+------------+-----------------------------------------+

Why is the count always 1? Because you have both a GROUP BY country_id clause and a window function. Naturally after the grouping has been done, there is one row per country_id. So when you partition by country_id, the count of each partition is 1.

You probably want the following:

mysql> select count(*), user_id, country_id, DATE(date), group_concat(search_keywords) as search_keywords from users_search_activity group by country_id;
+----------+---------+------------+------------+-----------------------------------------+
| count(*) | user_id | country_id | DATE(date) | search_keywords                         |
+----------+---------+------------+------------+-----------------------------------------+
|        6 |     132 |          0 | 2021-07-11 | inst,inst,inst,inst,ins,ins             |
|        2 |     132 |          2 | 2021-07-13 | xavie,ins                               |
|        1 |     132 |          3 | 2021-07-13 | ins                                     |
|        2 |     132 |          4 | 2021-07-13 | xavier ins,xavier insa                  |
|        2 |     132 |          5 | 2021-07-13 | xavier ins,ins                          |
|        2 |     132 |          6 | 2021-07-13 | xavier,xavier ins                       |
|        1 |     132 |          7 | 2021-07-13 | xavier ins                              |
|        1 |     132 |          8 | 2021-07-13 | xavier ins                              |
|        2 |     132 |          9 | 2021-07-13 | ins,xavier ins                          |
|        1 |       1 |         12 | 2021-07-14 | University Business Academy in Novi Sad |
+----------+---------+------------+------------+-----------------------------------------+

This uses an aggregate function COUNT(*) without a window function, so it counts the number of rows in the group, not the number of rows after applying the GROUP BY.

The latter query works on MySQL 5.7, since it contains no syntax that requires a later version.

2
  • Thanks for the answer. When using this query on 5.7 I am getting this error Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_289283206.users_search_activity.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    – newuser
    Sep 21 '21 at 4:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.