CODEWITHSUNDEEP
mysql
George
George

Reputation: 1114

Sum columns from multiple tables in MySQL

I am trying to sum some columns from multiple tables in just one SQL query, but I seem to be getting the wrong results. I think there is a problem with the code that I have provided below. Please any help on this is appreciated.

item Names
 | id | name |
 | 1  |  AB  |
 | 2  |  CA  |

table1
| id  | interest | year |
| 1   |   20.00  | 2014 |
| 2   |   30.00  | 2013 |
| 1   |   10.00  | 2013 |

table2
| id  | deposit  | year |
| 1   |   10.00  | 2014 |
| 2   |   10.00  | 2014 |

This is the query that I tried:

    SELECT 
        a.name,
        b.year,
        sum(b.interest) as 'total'
    FROM
        `table1` b
    INNER JOIN
        `item names` a
    ON
        b.id=a.id
    GROUP BY
        b.id

UNION ALL

    SELECT 
        c.name,
        d.year,
        sum(d.deposit) as 'total'
    FROM
        `table2` d
    INNER JOIN
        `item names` c
    ON
        d.id=c.id
    GROUP BY
        d.id

EXPECTED RESULTS

UPDATE

I am trying to find the total sum of interest and deposit for a particular year and for a particular item

|name   | year  |   total    |
| AB    | 2014  |    30.00   |
| AB    | 2013  |    10.00   |
| CA    | 2013  |    30.00   |
| CA    | 2014  |    10.00   |

Upvotes: 1

Views: 4338

Answers (2)

xQbert
xQbert

Reputation: 35323

Perhaps... assuming table1 and table2 have same structure.

First I generate a set with the union values from one and two then we use a simple aggregate and a group by to sum the values by name and year.

SELECT I.Name, B.year, Sum(B.Total) 
FROM item I
INNER JOIN  
(SELECT * FROM table1 UNION select * FROM table2) B
  on B.ID = I.ID
GROUP BY I.Name, B.Year

Upvotes: 2

AdamMc331
AdamMc331

Reputation: 16691

In the query you have posted, you need to group by year also to get the results. Then, you can use UNION to get all of the rows from the first set, along with all of the rows from the second set:

SELECT a.name, b.year, SUM(b.interest) AS total
FROM names a
JOIN table1 b ON b.id = a.id
GROUP BY a.name, b.year
UNION
SELECT a.name, c.year, SUM(c.deposit) AS total
FROM names a
JOIN table2 c ON c.id = a.id
GROUP BY a.name, c.year;

However, this doesn't give you your final results, as names that appear in each table ('AB' for example) will appear twice. One row for the year in deposits, one row for the year in interests. To combine those, just use the above as a subquery, sum the totals and again group by name and date:

SELECT name, year, SUM(total) AS total
FROM(
  SELECT a.name, b.year, SUM(b.interest) AS total
  FROM names a
  JOIN table1 b ON b.id = a.id
  GROUP BY a.name, b.year
  UNION
  SELECT a.name, c.year, SUM(c.deposit) AS total
  FROM names a
  JOIN table2 c ON c.id = a.id
  GROUP BY a.name, c.year) temp
GROUP BY name, year;

Here is an SQL Fiddle example.

Upvotes: 1

Related Questions