MYSQL - using SUM, JOIN and GROUP BY-CodePudding

I have table users:

id |email            |credit
1  |[email protected]    |10
2  |[email protected]  |20

and table orders:

id|user_id|subtotal
1 |1      |10
2 |1      |20
3 |2      |30

I want to get summed credit per user when orders are joined

with query:

SELECT SUM(credit) 
from users 
group by users.id

I get correct results

10
20

but with query(when join is added):

SELECT SUM(credit) 
from users 
JOIN orders on orders.user_id = users.id 
group by users.id

I get results:

20
20

Credit is doubled for user with id = 1(because there are 2 orders)

How to rewrite query to get desired results?

I need to join orders to fetch some that from that table.

PS. I need to use SUM because I also want to group by day, week, month, etc. not just by users.id

CodePudding user response：

There is absolutely no need to use sum:

SELECT users.id, users.credits 
FROM users 
JOIN orders ON orders.user_id = users.id 
GROUP BY users.id

If MySQL complains about "column credits not contained within aggregate" then wrap it inside any_value function; just don't use sum.

CodePudding user response：

here's one way to do it:

SELECT users.id,  
    min(credits) as credits
from users 
JOIN orders on orders.user_id = users.id 
group by users.id

id|credits|
-- ------- 
 1|10     |
 2|20     |

-> please check fiddle here

CodePudding user response：

Here is one solution that works:

SELECT 
    users.id,
    SUM(
        IF(orders.id = (
           SELECT 
                id
            FROM
                orders o
            WHERE
                o.user_id = users.id
            LIMIT 1),
        users.credit,
        0))
FROM
    users
        JOIN
    orders ON orders.user_id = users.id
GROUP BY users.id