2

I'd like to find the sum of a column in a single query given joins between multiple tables.

I have a table of Activities, and a table that maps Users performing an Activity, as well as a table mapping Teams to performed Activities. Both Users and Teams can perform the same activity multiple times.

Each activity is worth a set number of points, and I'd like to know the total number of points for a given user by totalling their activities with their team's activities.

I've tried various combinations of joins between the three tables, but cannot work out the correct query to total the points for a given user.

The following SQL will create a minimal version of this setup:

CREATE TABLE `activity` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL DEFAULT '',
  `points` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

CREATE TABLE `team_action` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `activity_id` INT(11) UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `team_action_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=INNODB;

CREATE TABLE `user_action` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `activity_id` INT(11) UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_action_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=INNODB;

INSERT INTO `activity` (`id`, `name`, `points`)
VALUES (1,'Running',10), (2,'Swimming',20), (3,'Hiking',30), (4,'Cycling',40);

INSERT INTO `team_action` (`id`, `activity_id`, `date`)
VALUES (1,2,'2012-05-22 14:32:31'), (2,4,'2012-05-22 14:32:36');

INSERT INTO `user_action` (`id`, `activity_id`, `date`)
VALUES (1,1,'2012-05-22 14:32:08'), (2,1,'2012-05-22 14:32:18'), (3,3,'2012-05-22 14:32:23');
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Mathew Byrne
  • 3,713
  • 5
  • 25
  • 23
  • what is the relationship user and team? I think something missing. cant find a relationship between `team_action` and `user_action` to get sum for a user. – Darshana May 22 '12 at 05:16
  • There are tables for both the User and the Team, and the User has a foreign key to a Team. For this example I don't believe that showing this relationship is strictly necessary? Essentially I'm only interested in how I can sum the totally correctly for a set of user_action rows and team_action rows. Does that make sense? I can expand on the schema if you think it will help. – Mathew Byrne May 22 '12 at 05:35

1 Answers1

1

It is not clear from the table definitions how users are related to teams (i.e. for a user, how do you know which is "their" team?) But I think the key to summing the points will be to use SUM on the result of UNION ALL in a subquery.

Something along the lines of:

SELECT SUM(points) AS total
FROM
(SELECT points
 FROM team_action JOIN activity ON(activity.id = team_action.activity_id)
 WHERE team_action.id = my_team
 UNION ALL
 SELECT points
 FROM user_action JOIN activity ON(activity.id = user_action.activity_id)
 WHERE user_action.id = my_user) me_and_team
Mathew Byrne
  • 3,713
  • 5
  • 25
  • 23
gcbenison
  • 11,723
  • 4
  • 44
  • 82
  • Thanks, that appears to work correctly. I intentionally omitted that relationship to simplify the test case, but you're assumption about the foreign keys is pretty much correct. – Mathew Byrne May 22 '12 at 05:44