0

I have two tables, one user table with the user information and one transaction table with transaction information that are all linked to a user. A transaction can have multiple types. What I'm trying to do is get the SUM of every transaction type for every user.

However, I've been trying to do this with joins this has left me with peculiar results.

Transactions

enter image description here

Users

enter image description here

Expected Result

A user row having the following sums per type;

  • 0 => 47.5
  • 1 => -47.5
  • 4 => -4.4

Gotten Result

enter image description here

I executed the following query;

SELECT u.username,
       Sum(t1.amount) AS t1Sum,
       Sum(t2.amount) AS t2Sum,
       Sum(t3.amount) AS t3Sum
FROM   users u
       JOIN transactions t1
         ON t1.user_id = u.id
            AND t1.type = 0
            AND t1.id <= 16
       JOIN transactions t2
         ON t2.user_id = u.id
            AND t2.type = 1
            AND t2.id <= 16
       JOIN transactions t3
         ON t3.user_id = u.id
            AND t3.type = 4
            AND t3.id <= 16
WHERE  u.id = 4

What I think is happening is that the transaction rows are being joined on itself causing extra rows of which the query takes the sum, I'm just not sure on how to solve it.

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
Vizious Developer
  • 81
  • 1
  • 7

1 Answers1

1

Its a Pivot scenario:

try this,

SELECT u.username,
       sum(case when t1.type=0 then t1.amount else 0 end) AS t1Sum,
       sum(case when t1.type=1 then t1.amount else 0 end) AS t2Sum,
       sum(case when t1.type=4 then t1.amount else 0 end) AS t3Sum
FROM   users u
       JOIN transactions t1
         ON t1.user_id = u.id
            AND t1.id <= 16
WHERE  u.id = 4 
group by u.username
Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32