CODEWITHSUNDEEP
mysql
Pr0metheus
Pr0metheus

Reputation: 37

How to fix this simple MySQL query

I have a small problem - I'm learning mysql and I can't wrap my head around the problem I'm trying to solve. I created 2 tables - first has users (id, name , birth) - second has transactions ( id , user_id , deposit , withdraw, date)

I have a simple query that should return names with deposits and withdraws from each month from the past 6 months.

Currently I have 5 entries in users with dummy data generated for transactions.

I've tried to switch things up abit in the query but nothing returns more than 1 name. I've checked stackoverflow search but the amount of information seems endless yet I've yet to find the answer I'm looking for (for my specific problem).

SELECT
    T1.name,
    T2.deposit,
    T2.withdraw,
    DATE_FORMAT(T2.date, '%M-%Y') transactions
FROM
    users AS T1
INNER JOIN transactions AS T2
ON
    T1.id = T2.user_id
WHERE
    DATE_FORMAT(T2.date, '%m') BETWEEN '06' AND '12'
GROUP BY
    DATE_FORMAT(t2.date, '%m');

I expect each name to display transactions made from each month

but the result is the following

http://prntscr.com/mfe0zs

so just results from the first deposit or withdraw that he can find for the month and just for the first name.

Upvotes: 0

Views: 30

Answers (1)

JNevill
JNevill

Reputation: 50034

YOu want to add a Sum() around your deposit and withdrawal. Also add name to the GROUP BY:

SELECT
    T1.name,
    Sum(T2.deposit),
    Sum(T2.withdraw),
    DATE_FORMAT(T2.date, '%M-%Y') transactions
FROM
    users AS T1
INNER JOIN transactions AS T2
ON
    T1.id = T2.user_id
WHERE
    DATE_FORMAT(T2.date, '%m') BETWEEN '06' AND '12'
GROUP BY
    DATE_FORMAT(t2.date, '%m'), t1.name;

Upvotes: 2

Related Questions