SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `type` varchar(25) DEFAULT NULL, `user_id` int(11) NOT NULL, `website` varchar(25) DEFAULT NULL, `data` varchar(25) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `events` (`type`, `user_id`, `website`, `data`, `created_at`) VALUES ('pageview', 1, 'example.com', '/', '2022-02-16 22:00:00'), ('pageview', 2, 'example.com', '/path', '2022-02-16 22:00:00'), ('purchase', 3, 'example.com', '2222', '2022-02-16 22:00:00'), ('view', 3, 'example.com', 'product', '2022-02-16 22:00:00'), ('click', 3, 'example.com', 'card', '2022-02-16 22:00:00'), ('pageview', 4, 'site.com', '/', '2022-02-16 22:00:00'), ('click', 4, 'site.com', 'product', '2022-02-16 22:00:00'), ('custom', 5, 'example1.com', 'test', '2022-02-16 22:00:00'), ('custom2', 6, 'google.com', 'test', '2022-02-16 22:00:00'), ('custom3', 5, 'example11.com', 'test', '2022-02-16 22:00:00'); SELECT * FROM `events`; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(events.type = "', type, '") AS `', events.type, '`') ) INTO @sql FROM `events`; SET @sql = CONCAT(' SELECT website,', @sql, ' FROM events GROUP BY website ORDER BY pageview DESC '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;