CODEWITHSUNDEEP
mysqldatabase
Zamereon
Zamereon

Reputation: 386

MySQL Sum Columns from Two Queries

sqlfiddle link

I'm working on an armor builder app where a character can have skills on both armor and charms, and I'm trying to sum them up so the user will know the total of their selected pieces. I have a query that sums the armors and a query that sums the charms, but I need a way to combine them. Here is what I have so far:

The query to get the armor skills:

SELECT skills.id AS skillId, skills.name as skillName, SUM(armor_skills.ranks) as ranks from skills
INNER JOIN armor_skills ON armor_skills.skill_id = skills.id
WHERE armor_skills.armor_id in (457,458,459,460,461)
GROUP BY skillId, skillName;

Which returns: 

7  | Skill One    | 3
39 | Skill Two    | 5
63 | Skill Three  | 1
71 | Skill Four   | 1
76 | Skill Five   | 3

And then I have this query to get the charms skills:

SELECT skills.id AS skillId, skills.name AS skillName, SUM(skill_ranks.rank) AS ranks FROM charm_skills
INNER JOIN charm_ranks ON charm_skills.charm_rank_id = charm_ranks.id
INNER JOIN skills ON charm_skills.skill_id = skills.id
INNER JOIN skill_ranks ON skills.id = skill_ranks.skill_id AND charm_ranks.rank = skill_ranks.rank
WHERE charm_ranks.id = 57
GROUP BY skillId, skillName;

Which returns:

39 | Skill Two | 3

I am able to Union the two queries together like this: 

(SELECT skills.id AS skillId, skills.name AS skillName, SUM(armor_skills.ranks) AS ranks FROM skills
INNER JOIN armor_skills ON armor_skills.skill_id = skills.id
WHERE armor_skills.armor_id IN (457,458,459,460,461)
GROUP BY skillId, skillName)
UNION
(SELECT skills.id AS skillId, skills.name AS skillName, SUM(skill_ranks.rank) AS ranks FROM charm_skills
INNER JOIN charm_ranks ON charm_skills.charm_rank_id = charm_ranks.id
INNER JOIN skills ON charm_skills.skill_id = skills.id
INNER JOIN skill_ranks ON skills.id = skill_ranks.skill_id AND charm_ranks.rank = skill_ranks.rank
WHERE charm_ranks.id = 57
GROUP BY skillId, skillName);

Which returns:

7  | Skill One    | 3
39 | Skill Two    | 5
63 | Skill Three  | 1
71 | Skill Four   | 1
76 | Skill Five   | 3
39 | Skill Two    | 3

But I am trying to get:

7  | Skill One    | 3
39 | Skill Two    | 8
63 | Skill Three  | 1
71 | Skill Four   | 1
76 | Skill Five   | 3

Upvotes: 0

Views: 50

Answers (1)

Raj006
Raj006

Reputation: 562

I think your question is similar to this post Group by with union mysql select query. Use a select wrapper around your union (use union all) with from as seen in the post with a group by at the end. I did not test it, but it should be similar to the following.

SELECT skillId, skillName, SUM(ranks) from 
((SELECT skills.id AS skillId, skills.name AS skillName, 
SUM(armor_skills.ranks) AS ranks FROM skills INNER JOIN armor_skills ON 
armor_skills.skill_id = skills.id WHERE armor_skills.armor_id IN 
(457,458,459,460,461) GROUP BY skillId, skillName) 
UNION ALL 
(SELECT skills.id AS skillId, skills.name AS skillName, SUM(skill_ranks.rank) AS 
ranks FROM charm_skills INNER JOIN charm_ranks ON charm_skills.charm_rank_id 
= charm_ranks.id INNER JOIN skills ON charm_skills.skill_id = skills.id 
INNER JOIN skill_ranks ON skills.id = skill_ranks.skill_id AND 
charm_ranks.rank = skill_ranks.rank WHERE charm_ranks.id = 57 GROUP BY 
skillId, skillName)) t 
GROUP BY skillId,skillName;

Upvotes: 1

Related Questions