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.
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
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