MySQL - Sum from another table

Question

I want to run a select statement that includes a sum from another table. I know I will probably have to setup a join but I'm not sure how to approach it.

I want to select a list of accounts then get their balances(sum from another table) This is the idea I have been going in but obviously its not correct.

SELECT
    account_name
FROM
    tblaccounts
    JOIN (
        SELECT
            SUM(balance) AS account_balance
        FROM
            tblinvoices
    ) t ON t.account_id = tblaccount_account_id
WHERE
    tblaccounts.account_id = 1

desired output

Name   |   balance
------------------
Account1      50
Account2     100

peterm · Accepted Answer · 2013-07-03 00:01:05Z

11

Try

SELECT account_name, SUM(balance) balance
 FROM tblaccounts a LEFT JOIN tblinvoices i
   ON a.account_id = i.tblaccount_account_id
WHERE a.customer_id = 1
GROUP BY account_id

Output:

| ACCOUNT_NAME | BALANCE |
--------------------------
|     Account1 |      50 |
|     Account2 |     100 |

Here is SQLFiddle demo

edited Jul 3, 2013 at 0:01

answered Jul 2, 2013 at 23:55

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peterm Over a year ago

You are very welcome :) If it was what you were looking for please, consider to accept the answer.

Jose Manuel Abarca Rodríguez Over a year ago

Why is it left join and not just join?

Joe · Accepted Answer · 2023-02-07 17:59:04Z

1

You will need to create sub-queries to isolate each SUM otherwise you will return a SUM of every balance, not one for each. And it will be one row, not a row for each account.

SELECT 
     a.account_name,
    (
      SELECT SUM(i.balance) 
      FROM tblinvoices AS i 
      WHERE a.account_id = i.tblaccount_account_id
    ) AS balance
FROM tblaccounts AS a;

answered Feb 7, 2023 at 17:59

Joe

812 bronze badges

Collectives™ on Stack Overflow

MySQL - Sum from another table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related