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;
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 |