0

Category Table

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

Meta Table

mysql> SELECT * FROM meta;
+------+------+------+---------+-------------+-------+
| m_id | p_id | c_id | name    | description | costs |
+------+------+------+---------+-------------+-------+
|    1 |    1 |    1 | Abhijit | description | 100   |
|    2 |    1 |    1 | Abhijit | description | 200   |
|    3 |    1 |    2 | Abhiji2 | description | 500   |
+------+------+------+---------+-------------+-------+

Transaction Table

mysql> SELECT * FROM transactions;
+------+------+------+---------------------+--------+
| t_id | p_id | m_id | date                | amount |
+------+------+------+---------------------+--------+
|    1 |    1 |    1 | 2016-02-16 11:17:06 | 50     |
|    2 |    1 |    1 | 2016-02-16 11:17:06 | 50     |
|    3 |    1 |    2 | 2016-02-16 11:17:06 | 50     |
|    4 |    1 |    2 | 2016-02-16 11:17:06 | 150    |
+------+------+------+---------------------+--------+

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

I use:

mysql> SELECT c.*, SUM(t.amount), SUM(m.costs)
    FROM cats c
        LEFT JOIN meta m ON m.c_id=c.c_id
        LEFT JOIN transactions t ON t.m_id=m.m_id
    GROUP BY c.c_id;

+------+------+-----------+--------+---------------+--------------+
| c_id | p_id | c_name    | add_by | SUM(t.amount) | SUM(m.costs) |
+------+------+-----------+--------+---------------+--------------+
|    1 |    1 | Abhijit   |      1 |           100 |          400 |
|    2 |    1 | Abhiji2   |      1 |           200 |          500 |
+------+------+-----------+--------+---------------+--------------+

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 | p_id | c_name    | add_by | SUM(t.amount) | SUM(m.costs) |
+------+------+-----------+--------+---------------+--------------+
|    1 |    1 | Abhijit   |      1 |           100 |          300 |
|    2 |    1 | Abhiji2   |      1 |           200 |          500 |
+------+------+-----------+--------+---------------+--------------+
Improve this question
5
  • run the query without SUMs and GROUP to see whats wrong Commented Feb 16, 2016 at 5:49
  • You're selecting from con_cats in your SUM query, but you select from cats in your first query. Commented Feb 16, 2016 at 5:49
  • No No No just typing mistreat. table name is cats Commented Feb 16, 2016 at 5:51
  • @ABHIJIT an sql fiddle would have helped a lot for the answer. Commented Feb 16, 2016 at 5:53
  • I believe there is a problem in one of your JOIN conditions. It could be a typo. Commented Feb 16, 2016 at 5:55

2 Answers 2

Reset to default
1

I think you had a typo (or error) in one of your JOIN conditions. I think you intended your original query to be this:

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

Note carefully ON t.m_id = m.c_id, which agrees with your expected output. In any case, I reworked your query as follows:

SELECT c.c_id, c.p_id, c.c_name, t2.transactionCosts, t1.metaCosts
FROM cats c
LEFT JOIN
(
    SELECT c_id, SUM(costs) AS metaCosts
    FROM meta
    GROUP BY c_id
) t1
    ON c.c_id = t1.c_id
LEFT JOIN
(
    SELECT m_id, SUM(amount) AS transactionCosts
    FROM transactions
    GROUP BY m_id
) t2
    ON c.c_id = t2.m_id
WHERE t2.transactionCosts IS NOT NULL OR t1.metaCosts IS NOT NULL;

The first subquery computes the meta total for each c_id, and the second subquery computes the transaction total for each m_id. These results are then both joined together with the cats table to get your final result.

Follow the link below for a running demo:

SQLFiddle

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

5 Comments

PLEASE can you explain this query for me to better understanding it.
or any use full link for this . PLEASE
@ABHIJIT I added an explanation. Feel free to test each piece of my query separately in Fiddle so that you can understand what is happening.
Can you tell me the best way to learn sql(any book for website or tutorials) More deepth. PLEASE
The best way to learn SQL is by doing it in real life. That failing, answering/studying Stack Overflow question is a good alternative. Tech on the Net is a very good site which I use often.
0

the problem is you select c.* but only group by c_id, in this case you have 2 options. window function or subquery.

via over(partition by):

SELECT c.*, 
       SUM(t.amount)over(partition by c.c_id) as amount,
       SUM(m.costs)over(partition by c.c_id) as cost
FROM con_cats c
    LEFT JOIN meta m ON m.c_id=c.c_id
    LEFT JOIN transactions t ON t.m_id=m.m_id;

via subquery:

select a.*,b.amount,b.costs from con_cats a 
inner join
(SELECT c.c_id, SUM(t.amount) as amount, SUM(m.costs) as costs
FROM con_cats c
    LEFT JOIN meta m ON m.c_id=c.c_id
    LEFT JOIN transactions t ON t.m_id=m.m_id
GROUP BY c.c_id) b
on a.c_id = b.c_id;
Improve this answer

3 Comments

He is using MySQL, so I don't believe he will have window functions at his disposal.
@ABHIJIT you tried both? i didn't notice the mysql so window function will go wrong, but the subquery should work
@ABHIJIT oops, it's c_id, sorry about that. get it done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.