CODEWITHSUNDEEP
mysql
Sunish Menon
Sunish Menon

Reputation: 162

MySQL Multiple Subquery on same table

I have a table of the following structure

ID | Amount | Bank (1 or 2)
---+--------+------
1  | 100000 | 1
2  | 256415 | 2
3  | 142535 | 1
1  | 214561 | 2
2  | 123456 | 1
1  | 987654 | 2

I want a result like this (from the same table):

ID | sum(Bank 1) | sum(Bank 2)
---+-------------+------------
1  | 100000      | 1202215
2  | 123456      | 256415
3  | 142535      | 0

What will be the easiest query to achieve this?

Upvotes: 0

Views: 858

Answers (3)

JMC
JMC

Reputation: 930

This SQL query will pull the information you are looking for:

select ID, 
       SUM(IF(Bank=1, Amount, 0)) AS Bank1,
       SUM(IF(Bank=2, Amount, 0)) AS Bank2
from TableName
group by ID ASC

Upvotes: 1

Kyra
Kyra

Reputation: 5407

Using sqlFiddle with tsql I was able to come up with:

select distinct t.id,
      isnull((select sum(t1.amount) 
         from temp as t1
         where t.id = t1.id and t1.bank = 1), 0) as 'bank 1 sum',
      isnull((select sum(t1.amount) 
         from temp as t1
         where t.id = t1.id and t1.bank = 2), 0) as 'bank 2 sum'
  from temp as t

where temp is your table name. For mySQL (Thanks to @JakeFeasel in the comments):

select distinct t.id,
      ifnull((select sum(t1.amount) 
         from temp as t1
         where t.id = t1.id and t1.bank = 1), 0) as 'bank 1 sum',
      ifnull((select sum(t1.amount) 
         from temp as t1
         where t.id = t1.id and t1.bank = 2), 0) as 'bank 2 sum'
  from temp as t

Upvotes: 1

Thomas
Thomas

Reputation: 64635

The ANSI solution:

Select Id
  , Sum( Case When Bank = 1 Then Amount End ) As Bank1Total
  , Sum( Case When Bank = 2 Then Amount End ) As Bank2Total
From SourceTable
Group By Id

SQL Fiddle Version (using MySQL)

Upvotes: 2

Related Questions