I have 3 tables : accounts, contacts, contact_activity. Accounts can have multiple contacts, also multiple activities whereas contacts can also have multiple activities. adding tables queries below:
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;
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');
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;
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');
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 `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');
I have written the following query to get the required output but it's not working:
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
)
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;
the above query showing this result:
name | accountId | totalActivityScore | activityid
Vodafone Germany | 1024 | 12 | 2451
Microsoft | 1336 | 24 | 2442
which is incorrect as for accountId 1336 we have only 2 entries in contact_activity and there sum is 6 but it's showing 24.
I need the following output :
name | accountId | totalActivityScore | activityid
Vodafone Germany | 1024 | 12 | 2451
Microsoft | 1336 | 6 | 2442
Appreciate any help. Thanks in advance.
The problem with your query is with the last OR which I think is intended to capture activity by company where no contact is involved BUT you aren't testing for that so 10 rows are aggregated when only 4 should be.
change to
OR
(contact_activity.accountId = accounts.id AND
contact_activity.cmpCode = 'imli02-0323' AND `contact_activity`.`accountId` IS NOT NULL and
contactid= 0
)
Note the query will fail if only_full_group_by is set