CODEWITHSUNDEEP
mysql
MikeP
MikeP

Reputation: 195

Multiple select statements with SUM() and WHERE?

This involves three columns 'name', 'trans_status', 'amount'. I need to get a table where each 'name' is on one row, and there's a SUM() of all amounts for each of four statuses (new, pending, final, closed).

Here's what I'm looking for:

name           total_new     total_pending       total_final      total_closed
Frank          145.35        219.34              518.23           9588.33
Susan          233.54        455.44              920.00           9600.52

Here's what my table looks like:

transactions
================
userid        status        amount
----------------------------------
1             new           25.00
1             new           30.18
2             final         90.12
1             pending       100.25
2             new           81.43

users
================
userid        name
----------------------------------
1             Frank
2             Susan

I've tried a bunch of different queries, but I'm afraid I'm beyond my abilities. Here's one failing example:

    SELECT a.userid, u.name, 
    ( SUM(a.amount)
      WHERE a.status = 'new' 
    ) AS total_new,
    ( SUM(a.amount)
      WHERE a.status = 'pending' 
    ) AS total_pending,
    ( SUM(a.amount)
      WHERE a.status = 'final' 
    ) AS total_final,
    ( SUM(a.amount)
      WHERE a.status = 'closed' 
    ) AS total_closed 
    FROM transactions AS a
    LEFT JOIN users AS u ON u.userid = a.userid
    GROUP BY u.name
    ORDER BY u.name ASC;

Thanks for the help!

Upvotes: 2

Views: 3778

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56357

select u.name,
sum(case when status = 'new' then amount else 0 end) as total_new,
sum(case when status = 'pending' then amount else 0 end) as total_pending,
sum(case when status = 'final' then amount else 0 end) as total_final,
sum(case when status = 'closed' then amount else 0 end) as total_closed
from users as u
left join transactions as t on u.userid = t.userid
group by u.name

Upvotes: 6

mgraph
mgraph

Reputation: 15338

am not sure try this:

SELECT a.userid, u.name, 
    (SELECT SUM(amount) AS total_new FROM transactions
      WHERE status = 'new' 
    ),
    (SELECT SUM(amount) AS total_pending FROM transactions
      WHERE status = 'pending' 
    ),
    (SELECT SUM(amount) AS total_final FROM transactions
      WHERE status = 'final' 
    ),
    (SELECT SUM(amount) AS total_closed FROM transactions
      WHERE status = 'closed' 
    ) 
    FROM transactions AS a
    LEFT JOIN users AS u ON u.userid = a.userid
    GROUP BY u.name
    ORDER BY u.name ASC;

Upvotes: 0

Related Questions