mysql joining 3 tables with a group by group by

231
March 01, 2018, at 3:21 PM

I have three tables

users:
+----+--------+--------------+
| id | name   | user_type_id |
+----+--------+--------------+
|  1 | Tawsif |            1 |
|  2 | Karim  |            2 |
+----+--------+--------------+
transactions:
+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
|  1 |       1 |     10 |
|  2 |       2 |     10 |
|  3 |       1 |     10 |
+----+---------+--------+
course_fee:
+----+---------+-----+
| id | user_id | fee |
+----+---------+-----+
|  1 |       1 | 105 |
|  2 |       2 |  33 |
|  3 |       1 | 106 |
+----+---------+-----+

I want to get the count of total_rows, sum of each users transaction amount and fees from both transactions and course fees table

So I tried this

SELECT users.id, SUM(transactions.amount) as total_transaction_amount,
count(transactions.id) as transaction_count, 
sum(course_fee.fee) as total_fee, 
count(course_fee.user_id) as total_fee_count 
    FROM users 
INNER join transactions on transactions.user_id = users.id 
INNER join course_fee on course_fee.user_id = users.id 
    GROUP by users.id

result:

+----+--------------------------+-------------------+-----------+-----------------+
| id | total_transaction_amount | transaction_count | total_fee | total_fee_count |
+----+--------------------------+-------------------+-----------+-----------------+
|  1 |                       40 |                 4 |       422 |               4 |
|  2 |                       10 |                 1 |        33 |               1 |
+----+----------

It returned Wrong results. How can i Fix this?

Answer 1

Your issue is a common problem occurring when trying to report aggregates from multiple tables related by a join. One sane way of handling this is to join to two separate subqueries, each of which aggregates transactions and fees separately:

SELECT
    u.id,
    u.name,
    COALESCE(t.amount, 0) AS total_transaction_amount,
    COALESCE(t.cnt, 0)    AS transaction_count,
    COALESCE(c.fee, 0)    AS total_fee,
    COALESCE(c.cnt, 0)    AS total_fee_count
FROM users u
LEFT JOIN
(
    SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS amount
    FROM transactions
    GROUP BY user_id
) t
    ON u.id = t.user_id
LEFT JOIN
(
    SELECT user_id, COUNT(*) AS cnt, SUM(fee) AS fee
    FROM course_fee
    GROUP BY user_id
) c
    ON u.id = c.user_id;

Note that we left join above because perchance a user does not appear in either the transaction or fee tables. In that case, we assign a value of zero to the sums and counts.

Answer 2

Correct Ans

   SELECT test.id,transaction_count,test.total_transaction_amount, 
    IFNULL(SUM(course_fee.fee),0) AS total_fee, 
    IFNULL(COUNT(course_fee.user_id),0) AS total_fee_count  FROM 
    (
      SELECT users.id, IFNULL(SUM(transactions.amount),0) AS 
      total_transaction_amount,
      IFNULL(COUNT(transactions.id),0) AS transaction_count
      FROM users 
      LEFT JOIN transactions ON transactions.user_id = users.id 
      GROUP BY users.id
    ) AS test 
    LEFT JOIN course_fee ON course_fee.user_id = test.id 
    GROUP BY test.id

This is what happening in your query after 2nd JOIN that's why you are getting 40 for id 1

Rent Charter Buses Company
READ ALSO
DotNetCore | The 'MySQLNumberTypeMapping' does not support value conversions

DotNetCore | The 'MySQLNumberTypeMapping' does not support value conversions

We are using Entity framework (Code First) to read a Table in MySQL using AspNET core, and facing below exception while reading the table:

1023
Can I extra data from `.idx`, `.nr`, `.tit` files?

Can I extra data from `.idx`, `.nr`, `.tit` files?

I download a software, and in it there are some dictionariesin the directories, there are

126
Calculations cannot be performed by passed value from two different subroutines to a new subroutine

Calculations cannot be performed by passed value from two different subroutines to a new subroutine

I used an anonymous hash to pass value from two different subroutines to a new subroutineBut now I'm not able to perform calculations using the passed variables

154
mysql select all table data from two tables on two databases

mysql select all table data from two tables on two databases

I want to get all data from two table on different DatabasesI've tried this and only get data from one table

144