CODEWITHSUNDEEP
mysqldatabase
GEORGE Reed
GEORGE Reed

Reputation: 445

How do you combine two sum queries into one?

SELECT dep.depName, SUM(worker.salary) as total
FROM team, worker
WHERE worker.depID = dep.depID
GROUP BY dep.depID

SELECT dep.depName, SUM(manager.salary) as total
FROM manager, dep
WHERE manager.depID = dep.depID
GROUP BY team.depID

I tried something along the line of: SELECT dep.depName, SUM(manager.salary)+SUM(worker.salary) as total but it gave me some strange output, it adds N elements N number of time.

Upvotes: 0

Views: 127

Answers (3)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

What about something like this :

SELECT result.depName, SUM(worker.salary) as total, manager_total
FROM team, worker
INNER JOIN
    (SELECT dep.depID as depID, dep.depName as depName, SUM(manager.salary) as manager_total
    FROM dep
    LEFT OUTER JOIN manager ON manager.depID = dep.depID
    GROUP BY dep.depID, dep.depName) as result ON result.depID = team.depID
WHERE worker.depID = team.depID
GROUP BY result.depName, manager_total

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

The more efficient way is to sum the results by department without doing a join. Then join in the department name.

This method also allows you to separate the department salaries into the two groups:

select depId, sum(wsalary) as wsalary, sum(msalary) as msalary,
       sum(wsalary) + sum(msalary) as total
from ((select depId, sum(w.salary) as wsalary, null as msalary
       from worker w
       group by depId
      )
      union all
      (select depId, NULL as wsalary, sum(m.salary) as msalary
       from manager m
       group by depId
      )
     ) t join
     dep d
     on t.depId = d.depId
group by t.depId

I am assuming the team in the first from clause really refers to dep, as used in the rest of the query.

Upvotes: 0

dethtron5000
dethtron5000

Reputation: 10841

You need to do a subquery of a UNION statement

SELECT dep.depName, sum(salary) as total, dep.depID 
FROM
(
    (SELECT dep.depName, worker.salary as salary, dep.depID
        FROM team, worker
        WHERE worker.depID = dep.depID
    ) 
    UNION 
   (SELECT dep.depName, manager.salary as salary, dep.depID
       FROM manager, dep
       WHERE manager.depID = dep.depID
   )
) dep GROUP BY dep.depID

Upvotes: 1

Related Questions