Mysql Question posted in
The official MySQL documentation can be found here.

MySQL join 3 tables and having clause with SUM on activityScore column

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.

Tags:

2

Answers


  1. 0

    Please use mysql function to calculate total activity score of account.

    DELIMITER $$

DROP FUNCTION IF EXISTS `f_getTotalActivityScoreOfAccount`$$

CREATE FUNCTION `f_getTotalActivityScoreOfAccount`(_id INT 
    ) RETURNS INT(11)
BEGIN
    DECLARE _total INT DEFAULT 0;
    SELECT SUM(activityScore) FROM contact_activity WHERE accountId = _id AND contactID NOT IN (SELECT id FROM contacts WHERE accountId = _id AND is_deleted = 'yes') INTO _total;
    RETURN _total;
END$$

DELIMITER ;

    SELECT `accounts`.`companyName` AS `name`,
      `accounts`.`id` AS `accountId`,
    f_getTotalActivityScoreOfAccount(`accounts`.`id`) AS totalActivityScore,
    contact_activity.id AS activityid
    FROM
        `accounts`
    LEFT JOIN `contacts` ON `accounts`.`id` = `contacts`.`accountId` AND(
            FIND_IN_SET(
                'imli02-0323',
                contacts.cmpMultiple
            ) AND(
                FIND_IN_SET(
                    'imli02-0323',
                    accounts.cmpCodes
                ) 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;
    Login or Signup to reply.
  2. 0

    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

    https://dbfiddle.uk/PG8OclY6

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search