CODEWITHSUNDEEP
mysqlsql
Hamada
Hamada

Reputation: 517

How to sum a field for two times with two conditions in the same SQL Statements

I have the following SQL statements:

Select SUM (amount) from accounts Where acctype = 0;

and 

Select SUM (amount) from accounts Where acctype = 1;

I want to find the difference between the amount in two cases in a single SQL statement.

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use conditional aggregation:

select sum(case when acctype = 0 then amount
                when acctype = 1 then - amount
           end) as diff
from accounts;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

This is called conditional aggregation:

SELECT
  SUM(case when acctype = 0 then amount else 0 end) AS sum0,
  SUM(case when acctype = 1 then amount else 0 end) AS sum1,
  SUM(case when acctype = 0 then amount else 0 end) -
  SUM(case when acctype = 1 then amount else 0 end) AS diff
FROM accounts;

Upvotes: 1

Related Questions