CODEWITHSUNDEEP
sqlmysql
Tareq
Tareq

Reputation: 2006

MySQL Query Related Problem

setup: 

mysql> create table bank(bank_id integer, bank_name varchar(255));
Query OK, 0 rows affected (0.27 sec)

mysql> create table accounts_bank(method tinyint, bank_id integer, amount float);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bank(bank_id, bank_name) values(1, 'A Bank');
Query OK, 1 row affected (0.05 sec)

mysql> insert into bank(bank_id, bank_name) values(2, 'B Bank');
Query OK, 1 row affected (0.03 sec)

mysql> insert into bank(bank_id, bank_name) values(3, 'C Bank');
Query OK, 1 row affected (0.03 sec)

mysql> insert into bank(bank_id, bank_name) values(4, 'D Bank');
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 1, 2500);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (1, 2, 2500);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (1, 3, 5000);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 4, 500);
Query OK, 1 row affected (0.05 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 4, 5800);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (1, 3, 25000);
Query OK, 1 row affected (0.02 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 2, 27500);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 1, 2000);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 1, 2500);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (1, 2, 2500);
Query OK, 1 row affected (0.03 sec)

mysql> inseert into accounts_bank(method, bank_id, amount) values (0, 4, 2500);
Query OK, 1 row affected (0.03 sec)

my first query: 

mysql> select bank_name, sum(amount) as amount from accounts_bank ab left join bank b on b.bank_id=ab.bank_id where method=0 group by bank_name order by bank_name;

returns: 

+-----------+--------+ 
| bank_name | amount | 
+-----------+--------+ 
| A Bank    |   7000 | 
| B Bank    |  27500 | 
| D Bank    |   8800 |
+-----------+--------+ 
3 rows in set (0.00 sec)

my second query: 

mysql> select bank_name, sum(amount) as amount from accounts_bank ab left join bank b on b.bank_id=ab.bank_id where method=1 group by bank_name order by bank_name;

returns: 

+-----------+--------+ 
| bank_name | amount | 
+-----------+--------+ 
| B Bank    |   5000 | 
| C Bank    |  30000 |
+-----------+--------+ 
2 rows in set (0.00 sec)

Now I want a result like this by simply deducting 2nd result from the 1st one: 

+-----------+--------+ 
| bank_name | amount | 
+-----------+--------+ 
| A Bank    |   7000 | 
| B Bank    |  22500 |
| C Bank    | -30000 | 
| D Bank    |   8800 |
+-----------+--------+

To get this result what mysql query should I run?

Upvotes: 3

Views: 207

Answers (3)

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

You can use the CASE expression in this case:

SELECT
    bank_name,
    sum(CASE WHEN method=0 THEN amount WHEN method=1 THEN -amount ELSE 0 END) AS amount
FROM accounts_bank ab
    LEFT JOIN bank b ON b.bank_id=ab.bank_id
GROUP BY bank_name
ORDER BY bank_name

If you need some more complex processing, you can simply combine the two queries like this:

SELECT bank_name, sum(amount) AS amount
FROM
  (SELECT bank_id, sum(amount) as amount
   FROM accounts_bank WHERE method=1
   UNION
   SELECT bank_id, -sum(amount) as amount
   FROM accounts_bank WHERE method=0) ab
  LEFT JOIN bank b ON b.bank_id=ab.bank_id
GROUP BY bank_name
ORDER BY bank_name

Upvotes: 2

Jake Wilson
Jake Wilson

Reputation: 91183

I notice that you are setting 'bank_id' = 1 for all the banks. Shouldn't they be different numbers? This might be part of your problem.

Upvotes: 0

Asaph
Asaph

Reputation: 162771

select subquery.bank_name, sum(subquery.amount) from
    (
    select bank_name, sum(amount) as amount from accounts_bank ab left join bank b on
        b.bank_id=ab.bank_id where method=0 group by bank_name
    union all
    select bank_name, sum(-amount) as amount from accounts_bank ab left join bank b on
        b.bank_id=ab.bank_id where method=1 group by bank_name
    ) as subquery group by subquery.bank_name order by subquery.bank_name

Upvotes: 2

Related Questions