CODEWITHSUNDEEP
mysqlsumleft-join
T. Karter
T. Karter

Reputation: 738

How to sum up two different table values

I have two tables:

data
id[int]    balance[float]    category[id]
   1            10.2              1
   2            0.12              2
   3           112.42             1
   4            2.3               3

categories
id[int]    name[varchar]    start_at[float]
   1            high             10.5
   2            low             105.2
   3            mid              0.7

I want to query the categories and join the data. For each categorie I want the sum of all data balances added to the start_at value of categories:

This is where I started with:

select sum(d.balance) as balancesum, c.name
from data d
left join categories c on c.id = d.category
group by d.category

What I want to know is, how can I add the start_at value of categories to the balancesum value?

Upvotes: 1

Views: 48

Answers (2)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can use next approach:

select 
    c.name, balancesum, ifnull(balancesum, 0) + start_at
from categories c
left join (
    -- calculate sum of balances per category
    -- and join sums to data table
    select category, sum(d.balance) as balancesum
    from data d
    group by d.category
) b on b.category = c.id;

Here you can play with live query

Upvotes: 2

Akina
Akina

Reputation: 42611

SELECT c.name, c.start_at + SUM(d.balance) as balancesum
FROM categories c
JOIN data d ON c.id = d.category
GROUP BY c.name, c.start_at

Upvotes: 2

Related Questions