CODEWITHSUNDEEP
mysqlsqlgroup-by
Mayeenul Islam
Mayeenul Islam

Reputation: 4762

How to group by data and calculate at a time in mySQL?

======================================
| InputType | Payable | Amount | pID |
======================================
| Expenses  | 10,000  | 5,000  |  1  |
--------------------------------------
| Expenses  | 10,000  | 5,000  |  1  |
--------------------------------------
| Expenses  | 10,000  | 5,000  |  2  |
--------------------------------------
| Deposit   | 5,000   | 4,000  |  1  |
--------------------------------------
| Deposit   | 15,000  | 10,000 |  2  |
--------------------------------------

If I want to view the balance like this from the above table:

==============================
| pID | Balance1  | Balance2 |
==============================
| 1   |  24,000  |   15,000  |
------------------------------
| 2   |  20,000  |   20,000  |
------------------------------

Calculation for pID 1:

Payable (Expenses) 10,000+10,000 + Amount (Deposit) 4,000 = Balance1 24,000

Payable (Expenses) 5,000+5,000 + Amount (Deposit) 5,000 = Balance2 15,000

Calculation for pID 2:

Payable (Expenses) 10,000 + Amount (Deposit) 10,000 = Balance1 20,000

Payable (Expenses) 5,000 + Amount (Deposit) 15,000 = Balance2 20,000

Upvotes: 1

Views: 58

Answers (1)

fthiella
fthiella

Reputation: 49089

If InputType can have only two differents status, I think you are looking for something like this:

select
  pID,
  sum(case when InputType='Expenses' then Payable else Amount end) as Balance1,
  sum(case when InputType='Expenses' then Amount else Payable end) as Balance2
from tbl
group by pID

Upvotes: 1

Related Questions