MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE `accounts` (
  `id` int(10) UNSIGNED NOT NULL,
  `companyName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_duplicate` enum('yes','no') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'no',
  `cmpCodes` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` enum('yes','no') COLLATE utf8mb4_unicode_ci DEFAULT 'no',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `contacts` (
  `id` int(11) NOT NULL,
  `accountId` int(11) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `cmpMultiple` text DEFAULT NULL,
  `is_duplicate` enum('yes','no') NOT NULL DEFAULT 'no',
  `is_deleted` enum('yes','no') DEFAULT 'no',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




CREATE TABLE `contact_activity` (
  `id` int(11) NOT NULL,
  `contactID` int(11) NOT NULL,
  `accountId` int(11) DEFAULT NULL COMMENT 'PD org id',
  `cmpCode` varchar(255) DEFAULT NULL,
  `activityScore` int(11) DEFAULT NULL,
  `activityDate` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `accounts` (`id`, `companyName`, `is_duplicate`, `cmpCodes`, `is_deleted`, `created_at`, `updated_at`) VALUES
(1024, 'Vodafone Germany','no', 'imli02-0323', 'no', '2023-10-20 09:36:02', '2021-11-18 15:35:39'),
(1336, 'Microsoft', 'no', 'imli02-0323', 'no', '2023-11-23 20:02:03', '2021-12-20 14:16:29'),
(1234, 'Microsoft test','no', 'imli02-0323', 'no', '2023-11-23 20:02:03', '2021-12-20 14:16:29');

INSERT INTO `contacts` (`id`, `accountId`, `name`, `cmpMultiple`, `is_duplicate`, `is_deleted`, `created_at`, `updated_at`) VALUES
( 4543, 1336, 'alex', 'imli02-0323', 'no', 'no', '2023-08-15 15:13:22', '2023-11-20 10:24:44'),
( 4545, 1024, 'julie', 'imli02-0323', 'no', 'no', '2023-08-16 10:26:22', '2024-01-08 08:33:23'),
( 4742, 1336, 'Matt', 'imli02-0323', 'no', 'no', '2023-11-14 11:23:21', '2023-11-23 20:02:03'),
( 4744, 1336, 'Martin', 'imli02-0323', 'no', 'no', '2023-11-14 11:23:21', '2023-11-15 16:39:33'),
( 4743, 1336, 'Andrew', 'imli02-0323', 'no', 'no', '2023-11-14 11:23:22', '2023-11-23 19:02:48'),
( 3434, 1234, 'jack', 'imli02-0323', 'no', 'no', '2023-08-16 10:26:22', '2024-01-07 12:09:04');

INSERT INTO `contact_activity` (`id`, `contactID`, `accountId`, `cmpCode`, `activityScore`, `activityDate`, `created_at`) VALUES
(2442,  4543, 1336, 'imli02-0323', 3, '2023-08-15 16:13:35', '2023-08-15 17:13:36'),
(2451,  4545, 1024, 'imli02-0323', 8, '2023-08-16 11:27:24', '2023-08-16 12:27:25'),
(2695, 4543, 1336, 'imli02-0323', 3, '2023-10-09 16:24:50', '2023-10-09 17:24:51'),
(3116, 0, 1024, 'imli02-0323', 4, '2023-12-27 12:55:47', '2023-12-29 13:47:53');

Records: 3  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


SELECT  *
       #`accounts`.`companyName` AS `name`, `accounts`.`id` AS `accountId`,
       #SUM(contact_activity.activityScore) AS totalActivityScore,
       #contact_activity.id AS activityid
FROM   `accounts`
LEFT JOIN `contacts` ON `accounts`.`id` = `contacts`.`accountId` AND
        (`contacts`.`cmpMultiple` = 'imli02-0323' AND
	     (`accounts`.`cmpCodes` = 'imli02-0323' OR accounts.cmpCodes = '' OR accounts.cmpCodes IS NULL)) 
	 and `contacts`.`is_duplicate` = 'no' AND `contacts`.`is_deleted` = 'no'
INNER JOIN `contact_activity` ON
        #(`contact_activity`.`contactID` = `contacts`.`id` AND 
		#  `contact_activity`.`cmpCode` = 'imli02-0323' AND `contact_activity`.`contactID` IS NOT NULL
        #) 
		#  OR  
		  (contact_activity.accountId = accounts.id AND 
		   contact_activity.cmpCode = 'imli02-0323' AND `contact_activity`.`accountId` IS NOT NULL 
          #and contactid= 0
        )
WHERE  `accounts`.`is_duplicate` = 'no' AND `accounts`.`is_deleted` = 'no'  AND `contact_activity`.`activityScore` != ''
;


id
companyName
is_duplicate
cmpCodes
is_deleted
created_at
updated_at
id
accountId
name
cmpMultiple
is_duplicate
is_deleted
created_at
updated_at
id
contactID
accountId
cmpCode
activityScore
activityDate
created_at


1024
Vodafone Germany
no
imli02-0323
no
2023-10-20 09:36:02
2021-11-18 15:35:39
4545
1024
julie
imli02-0323
no
no
2023-08-16 10:26:22
2024-01-08 08:33:23
3116
0
1024
imli02-0323
4
2023-12-27 12:55:47
2023-12-29 13:47:53

1024
Vodafone Germany
no
imli02-0323
no
2023-10-20 09:36:02
2021-11-18 15:35:39
4545
1024
julie
imli02-0323
no
no
2023-08-16 10:26:22
2024-01-08 08:33:23
2451
4545
1024
imli02-0323
8
2023-08-16 11:27:24
2023-08-16 12:27:25

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4743
1336
Andrew
imli02-0323
no
no
2023-11-14 11:23:22
2023-11-23 19:02:48
2695
4543
1336
imli02-0323
3
2023-10-09 16:24:50
2023-10-09 17:24:51

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4744
1336
Martin
imli02-0323
no
no
2023-11-14 11:23:21
2023-11-15 16:39:33
2695
4543
1336
imli02-0323
3
2023-10-09 16:24:50
2023-10-09 17:24:51

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4742
1336
Matt
imli02-0323
no
no
2023-11-14 11:23:21
2023-11-23 20:02:03
2695
4543
1336
imli02-0323
3
2023-10-09 16:24:50
2023-10-09 17:24:51

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4543
1336
alex
imli02-0323
no
no
2023-08-15 15:13:22
2023-11-20 10:24:44
2695
4543
1336
imli02-0323
3
2023-10-09 16:24:50
2023-10-09 17:24:51

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4743
1336
Andrew
imli02-0323
no
no
2023-11-14 11:23:22
2023-11-23 19:02:48
2442
4543
1336
imli02-0323
3
2023-08-15 16:13:35
2023-08-15 17:13:36

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4744
1336
Martin
imli02-0323
no
no
2023-11-14 11:23:21
2023-11-15 16:39:33
2442
4543
1336
imli02-0323
3
2023-08-15 16:13:35
2023-08-15 17:13:36

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4742
1336
Matt
imli02-0323
no
no
2023-11-14 11:23:21
2023-11-23 20:02:03
2442
4543
1336
imli02-0323
3
2023-08-15 16:13:35
2023-08-15 17:13:36

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4543
1336
alex
imli02-0323
no
no
2023-08-15 15:13:22
2023-11-20 10:24:44
2442
4543
1336
imli02-0323
3
2023-08-15 16:13:35
2023-08-15 17:13:36


SELECT  *
       #`accounts`.`companyName` AS `name`, `accounts`.`id` AS `accountId`,
       #SUM(contact_activity.activityScore) AS totalActivityScore,
       #contact_activity.id AS activityid
FROM   `accounts`
LEFT JOIN `contacts` ON `accounts`.`id` = `contacts`.`accountId` AND
        (`contacts`.`cmpMultiple` = 'imli02-0323' AND
	     (`accounts`.`cmpCodes` = 'imli02-0323' OR accounts.cmpCodes = '' OR accounts.cmpCodes IS NULL)) 
	 and `contacts`.`is_duplicate` = 'no' AND `contacts`.`is_deleted` = 'no'
INNER JOIN `contact_activity` ON
        (`contact_activity`.`contactID` = `contacts`.`id` AND 
		  `contact_activity`.`cmpCode` = 'imli02-0323' AND `contact_activity`.`contactID` IS NOT NULL
        ) 
		  OR  
		  (contact_activity.accountId = accounts.id AND 
		   contact_activity.cmpCode = 'imli02-0323' AND `contact_activity`.`accountId` IS NOT NULL 
          and  contactid= 0
        )
WHERE  `accounts`.`is_duplicate` = 'no' AND `accounts`.`is_deleted` = 'no'  AND `contact_activity`.`activityScore` != ''
;


id
companyName
is_duplicate
cmpCodes
is_deleted
created_at
updated_at
id
accountId
name
cmpMultiple
is_duplicate
is_deleted
created_at
updated_at
id
contactID
accountId
cmpCode
activityScore
activityDate
created_at


1024
Vodafone Germany
no
imli02-0323
no
2023-10-20 09:36:02
2021-11-18 15:35:39
4545
1024
julie
imli02-0323
no
no
2023-08-16 10:26:22
2024-01-08 08:33:23
3116
0
1024
imli02-0323
4
2023-12-27 12:55:47
2023-12-29 13:47:53

1024
Vodafone Germany
no
imli02-0323
no
2023-10-20 09:36:02
2021-11-18 15:35:39
4545
1024
julie
imli02-0323
no
no
2023-08-16 10:26:22
2024-01-08 08:33:23
2451
4545
1024
imli02-0323
8
2023-08-16 11:27:24
2023-08-16 12:27:25

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4543
1336
alex
imli02-0323
no
no
2023-08-15 15:13:22
2023-11-20 10:24:44
2695
4543
1336
imli02-0323
3
2023-10-09 16:24:50
2023-10-09 17:24:51

1336
Microsoft
no
imli02-0323
no
2023-11-23 20:02:03
2021-12-20 14:16:29
4543
1336
alex
imli02-0323
no
no
2023-08-15 15:13:22
2023-11-20 10:24:44
2442
4543
1336
imli02-0323
3
2023-08-15 16:13:35
2023-08-15 17:13:36


SELECT  #*
       `accounts`.`companyName` AS `name`, `accounts`.`id` AS `accountId`,
       SUM(contact_activity.activityScore) AS totalActivityScore,
       contact_activity.id AS activityid
FROM   `accounts`
LEFT JOIN `contacts` ON `accounts`.`id` = `contacts`.`accountId` AND
        (`contacts`.`cmpMultiple` = 'imli02-0323' AND
	     (`accounts`.`cmpCodes` = 'imli02-0323' OR accounts.cmpCodes = '' OR accounts.cmpCodes IS NULL)) 
	 and `contacts`.`is_duplicate` = 'no' AND `contacts`.`is_deleted` = 'no'
INNER JOIN `contact_activity` ON
        (`contact_activity`.`contactID` = `contacts`.`id` AND 
		  `contact_activity`.`cmpCode` = 'imli02-0323' AND `contact_activity`.`contactID` IS NOT NULL
        ) 
		  OR  
		  (contact_activity.accountId = accounts.id AND 
		   contact_activity.cmpCode = 'imli02-0323' AND `contact_activity`.`accountId` IS NOT NULL 
          and  contactid= 0
        )
WHERE  `accounts`.`is_duplicate` = 'no' AND `accounts`.`is_deleted` = 'no'  AND `contact_activity`.`activityScore` != ''
GROUP BY `contact_activity`.`accountId`,`accounts`.`id`
HAVING  SUM(contact_activity.activityScore) >= 6;

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle.accounts.companyName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


SELECT  #*
       `accounts`.`companyName` AS `name`, `accounts`.`id` AS `accountId`,
       SUM(contact_activity.activityScore) AS totalActivityScore ,
       group_concat(contact_activity.id) AS activityid
FROM   `accounts`
LEFT JOIN `contacts` ON `accounts`.`id` = `contacts`.`accountId` AND
        (`contacts`.`cmpMultiple` = 'imli02-0323' AND
	     (`accounts`.`cmpCodes` = 'imli02-0323' OR accounts.cmpCodes = '' OR accounts.cmpCodes IS NULL)) 
	 and `contacts`.`is_duplicate` = 'no' AND `contacts`.`is_deleted` = 'no'
INNER JOIN `contact_activity` ON
        (`contact_activity`.`contactID` = `contacts`.`id` AND 
		  `contact_activity`.`cmpCode` = 'imli02-0323' AND `contact_activity`.`contactID` IS NOT NULL
        ) 
		  OR  
		  (contact_activity.accountId = accounts.id AND 
		   contact_activity.cmpCode = 'imli02-0323' AND `contact_activity`.`accountId` IS NOT NULL 
          and  contactid= 0
        )
WHERE  `accounts`.`is_duplicate` = 'no' AND `accounts`.`is_deleted` = 'no'  AND `contact_activity`.`activityScore` != ''
GROUP BY `accounts`.`companyName` , `accounts`.`id` 
HAVING  SUM(contact_activity.activityScore) >= 6;


name
accountId
totalActivityScore
activityid


Microsoft
1336
6
2695,2442

Vodafone Germany
1024
12
3116,2451

id	companyName	is_duplicate	cmpCodes	is_deleted	created_at	updated_at	id	accountId	name	cmpMultiple	is_duplicate	is_deleted	created_at	updated_at	id	contactID	accountId	cmpCode	activityScore	activityDate	created_at
1024	Vodafone Germany	no	imli02-0323	no	2023-10-20 09:36:02	2021-11-18 15:35:39	4545	1024	julie	imli02-0323	no	no	2023-08-16 10:26:22	2024-01-08 08:33:23	3116	0	1024	imli02-0323	4	2023-12-27 12:55:47	2023-12-29 13:47:53
1024	Vodafone Germany	no	imli02-0323	no	2023-10-20 09:36:02	2021-11-18 15:35:39	4545	1024	julie	imli02-0323	no	no	2023-08-16 10:26:22	2024-01-08 08:33:23	2451	4545	1024	imli02-0323	8	2023-08-16 11:27:24	2023-08-16 12:27:25
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4743	1336	Andrew	imli02-0323	no	no	2023-11-14 11:23:22	2023-11-23 19:02:48	2695	4543	1336	imli02-0323	3	2023-10-09 16:24:50	2023-10-09 17:24:51
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4744	1336	Martin	imli02-0323	no	no	2023-11-14 11:23:21	2023-11-15 16:39:33	2695	4543	1336	imli02-0323	3	2023-10-09 16:24:50	2023-10-09 17:24:51
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4742	1336	Matt	imli02-0323	no	no	2023-11-14 11:23:21	2023-11-23 20:02:03	2695	4543	1336	imli02-0323	3	2023-10-09 16:24:50	2023-10-09 17:24:51
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4543	1336	alex	imli02-0323	no	no	2023-08-15 15:13:22	2023-11-20 10:24:44	2695	4543	1336	imli02-0323	3	2023-10-09 16:24:50	2023-10-09 17:24:51
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4743	1336	Andrew	imli02-0323	no	no	2023-11-14 11:23:22	2023-11-23 19:02:48	2442	4543	1336	imli02-0323	3	2023-08-15 16:13:35	2023-08-15 17:13:36
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4744	1336	Martin	imli02-0323	no	no	2023-11-14 11:23:21	2023-11-15 16:39:33	2442	4543	1336	imli02-0323	3	2023-08-15 16:13:35	2023-08-15 17:13:36
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4742	1336	Matt	imli02-0323	no	no	2023-11-14 11:23:21	2023-11-23 20:02:03	2442	4543	1336	imli02-0323	3	2023-08-15 16:13:35	2023-08-15 17:13:36
1336	Microsoft	no	imli02-0323	no	2023-11-23 20:02:03	2021-12-20 14:16:29	4543	1336	alex	imli02-0323	no	no	2023-08-15 15:13:22	2023-11-20 10:24:44	2442	4543	1336	imli02-0323	3	2023-08-15 16:13:35	2023-08-15 17:13:36