SQL - joining summarized data from multiple tables
I had a problem for several days. I managed to solve this, but performance worries me.
Basically I have a table of people and two tables ( debt
and wealth
) with links to person
. debt
/ wealth
tables can have multiple rows that refer to the same personID
.
I would need a result where I would simply have all the listed people with the sum of debt and wealth as my own columns.
First, let me introduce the tables I have:
Person table:
| ID | name |
|----|---------|
| 1 | Adam |
| 2 | Berg |
| 3 | Carl |
| 4 | David |
Wealth table:
| ID | personID | value |
|----|----------|----------|
| 1 | 1 | 100 |
| 2 | 1 | 2000 |
| 3 | 2 | 30000 |
| 4 | 3 | 400000 |
| 5 | 3 | 5000000 |
Debt table:
| ID | personID | value |
|----|----------|----------|
| 1 | 1 | 100 |
| 2 | 1 | 2000 |
| 3 | 2 | 30000 |
| 4 | 2 | 400000 |
| 5 | 3 | 5000000 |
Expected Result:
| personID | debtSum | wealthSum |
|----------|---------|-----------|
| 1 | 2100 | 2100 |
| 2 | 30000 | 430000 |
| 3 | 5400000 | 5000000 |
| 4 | (null) | (null) |
My decision:
SELECT SQL_NO_CACHE p.ID, debtSum, wealthSum
FROM person AS p
LEFT JOIN (SELECT personID, SUM(value) AS debtSum FROM debt GROUP BY personID) AS d ON d.personID = p.ID
LEFT JOIN (SELECT personID, SUM(value) AS wealthSum FROM wealth GROUP BY personID) AS w ON w.personID = p.ID
This query returns correct data, but as I said, performance worries me. For example, if I added thousands of rows to the debt table for a person who does not exist (for example, personID = 5), it takes a lot longer to complete the query. I think he also sums up all the data for this person, although this is not necessary for the result?
I am using SQL Server 2008 but is using MySQL (if it matters). SQL Fiddle
I would be grateful for tips on improving query performance. I'm running out of ideas.
source to share
Ok, here's how I would do it, although I would put the correct indexes, will have a bigger performance impact than the query structure:
EDIT COMMENTS:
SELECT ID, SUM(debtSum) AS debtSum, SUM(wealthSum) AS wealthSum
FROM (
SELECT p.ID, d.value AS debtSum, NULL AS wealthSum
FROM person AS p
LEFT JOIN debt d ON d.personID = p.ID
UNION ALL
SELECT p.ID, NULL AS debtSum, w.Value AS wealthSum
FROM person AS p
LEFT JOIN wealth w ON w.personID = p.ID
) t
GROUP BY t.ID
You must have indexes on Person.Id, Debt.PersonID and Wealth.PersonID
source to share