SQL SUM() different table different columns

Question

Category Table

mysql> SELECT * FROM cats;
+------+-----------+
| c_id | c_name    |
+------+-----------+
|    1 | cats 1    |
|    2 | cats 2    |
|    3 | cats 3    |
+------+-----------+

Meta Table

mysql> SELECT * FROM meta;
+------+------+----------+-------------+-------+
| m_id | c_id | name     | description | costs |
+------+------+----------+-------------+-------+
|    1 |    1 | Abhijit1 | description | 100   |
|    2 |    1 | Abhijit2 | description | 200   |
|    3 |    2 | Abhijit3 | description | 500   |
|    4 |    3 | Abhijit4 | description | 800   |
+------+------+----------+-------------+-------+

meta and cats table common is c_id meta table cats c_id(1) meta table have 2 (Abhijit1,Abhijit2) row with m_id(1,2)

Transaction Table

mysql> SELECT * FROM transactions;
+------+------+------------+--------+
| t_id | m_id | date       | amount |
+------+------+------------+--------+
|    1 |    1 | 2016-02-01 | 50     |
|    2 |    1 | 2016-02-06 | 50     |
|    3 |    3 | 2016-02-15 | 400    |
|    4 |    4 | 2016-02-19 | 150    |
+------+------+------------+--------+

transactions and meta table common is m_id transactions for m_id 1 have 2 row t_id(1,2) this table mainly for paid amount and date

I want to sum() for each category all costs (from meta table) and amount( from transaction table).

tables are connect with

cats.c_id
    |
    |-----> meta.c_id
    |-----> meta.m_id
                |-----> transactions.m_id

It's wrong. The Costs of cats id 1 is 300 but here I got 400

I Want Get Return From Query Like This:
+------+-----------+--------------+---------------+
| c_id | c_name    | SUM(m.costs) | SUM(t.amount) |
+------+-----------+--------------+---------------+
|    1 | cats 1    |          300 |           100 |
|    2 | cats 2    |          500 |           400 |
|    3 | cats 3    |          800 |           150 |
+------+-----------+--------------+---------------+

Here SUM(m.costs) are all Cost For a category and SUM(t.amount) are all paid for a category

Please help me or any better Idea for Table management.

Your question is confusing "The Costs of cats id 1 is 300 but here I got 400" cats id says 300 for m.costs and t.amount. Why do you say its 400? With the data provided shouldn't sum of t.amount be 100? — President Camacho
– President Camacho, Commented Feb 17, 2016 at 14:50

Giorgos Betsos · Accepted Answer · 2016-02-17 14:58:15Z

3

The problem arises from the fact that you join transactions so as to get SUM(t.amount). Hence, costs values are accounted for twice in case a single meta records is associated with two transactions record.

You can get around this problem using a correlated subquery to calculate SUM(t.amount):

SELECT c.c_id, 
       c.c_name, 
       SUM(m.costs),
       (SELECT SUM(t.amount)
        FROM transactions AS t
        WHERE m.m_id = t.m_id)
FROM cats AS c
LEFT JOIN meta AS m ON c.c_id = m.c_id
GROUP BY c.c_id, c.c_name

Output:

c_id c_name  SUM(m.costs)   SUM(t.amount) 
-----------------------------------------
1    cats 1  300            100
2    cats 2  500            400
3    cats 3  800            150

Demo here

answered Feb 17, 2016 at 14:58

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

4 Comments

ABHIJIT Over a year ago

Thank You So So Much For Your Answer

ABHIJIT Over a year ago

Can you sagest me that what the best way to learn mysql. php. javascript

ABHIJIT Over a year ago

any book tutorials. Please can you tell me how can i learn programming more deepth. Thanks again

Giorgos Betsos Over a year ago

@ABHIJIT The best way to learn programming is by doing, writing actual programs, building actual applications. You can also learn a lot here in SO. Check this book out for Javascript and JQuery.

sagi · Accepted Answer · 2016-02-17 15:00:09Z

0

Try this

SELECT cats.c_id,
       cats.c_name,
       (SELECT SUM(m.cost) FROM meta WHERE cats.c_id = m.c_id) AS cost,
      (SELECT SUM(t.cost) FROM meta WHERE t.id = m.id) AS amount
WHERE c.c_id = 1

edited Feb 17, 2016 at 15:00

sagi

40.6k7 gold badges66 silver badges87 bronze badges

answered Feb 17, 2016 at 14:58

Kamaldeep singh Bhatia

7326 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL SUM() different table different columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related