How to select rows from one table and order it by the sum of a column from another table?

0
votes

In MySQL, I have two tables. First is members. Second is member_experience. The member_experience table contains values of all the XP that members earn and what they get it for. I want to write a query that selects members and orders results by the total of their xp. The query that I have written doesn't return any results.

CREATE TABLE IF NOT EXISTS `members` (
  `member_id` int(15) NOT NULL AUTO_INCREMENT,
  `group_id` int(15) NOT NULL,
  `display_name` text NOT NULL,
  `email_address` text NOT NULL,
  `password` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  `activation_code` varchar(16) NOT NULL,
  `date_joined` text NOT NULL
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `member_experience` (
  `experience_id` int(15) NOT NULL AUTO_INCREMENT,
  `member_id` int(15) NOT NULL,
  `value` mediumint(6) NOT NULL,
  `description` text NOT NULL,
  `date_earned` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`experience_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

This is my current query:


       SELECT
          m.member_id,
          xp.member_id, SUM(xp.value) AS total_xp
       FROM members AS m
       LEFT JOIN member_experience AS xp ON (xp.member_id = m.member_id)
       ORDER BY total_xp";
2
mysqljoinsum

2 Answers

0
votes

This will do the stuff:

SELECT
  members.member_id,
  total_xp.sum_xp
FROM
  members
   LEFT JOIN
     (SELECT 
       member_id, 
       SUM(value) AS sum_xp 
     FROM 
       member_experience 
     GROUP BY 
       member_id) AS total_xp
     ON members.member_id = total_xp.member_id
ORDER BY
  total_xp.sum_xp
0
votes

try this:

SELECT
   m.member_id,
   xp.member_id, SUM(xp.value) AS total_xp
FROM 
   members AS m
LEFT JOIN 
   member_experience AS xp ON xp.member_id = m.member_id
GROUP BY 
   m.member_id
ORDER BY 
   SUM(xp.value)