CODEWITHSUNDEEP
sqlsql-server
Hanzou
Hanzou

Reputation: 73

SQL Server sum based on type

Titles:

Title        Type
Sample1      D+C-
Sample2      D+C-
Sample3      C+D-
Sample4      C+D-

The meaning of the type column are: D+C- = Debit - Credit C+D- = Credit - Debit

Transactions:

Type       Title       Amt
Debit      Sample1     200
Credit     Sample2     120
Debit      Sample1     110
Debit      Sample2     130
Credit     Sample3     150
Debit      Sample2     210
Debit      Sample3     250
Credit     Sample1     300
Credit     Sample4     220

I can sum per title but i can't compute based on the type of the title

SELECT
    title,
    SUM(amt) as amt
    type
FROM
    tbltran
GROUP BY
    title, type;

EDIT: I found another scenario that if there is only one credit transaction, thank you

The result must be:

Title       Amt
Sample1     10
Sample2     220
Sample3     -100
Sample4     220

Upvotes: 0

Views: 67

Answers (1)

Tyler Roper
Tyler Roper

Reputation: 21672

The question isn't entirely clear but I'm going to assume you're looking to return title and amt, where amt is the sum by title, the caveat being that debits and credits are either negative or positive according to the "Type" field in the "Titles" table.

Given your comment that there are only two types (D+C- and C+D-), we don't have to parse the query dynamically, but rather can just use some simple CASE logic.

Here are two options, either of which should work, however I don't have SQL Server available to me at the moment to compare them...

QUERY 1

SELECT
    t.title,
    SUM(
      CASE WHEN t.Type = 'Credit' THEN -1 ELSE 1 END
      *
      CASE WHEN tc.Type = 'D+C-' THEN 1 ELSE -1 END
      *
      amt
    ) as amt
FROM
    tbltran t
    INNER JOIN tbltitles tc ON tc.Title = t.Title
GROUP BY
    t.title;

QUERY 2

SELECT
    t.title,
    SUM(
       CASE 
          WHEN t.Type = 'Credit' AND tc.Type = 'D+C-' THEN -amt
          WHEN t.Type = 'Credit' AND tc.Type = 'C+D-' THEN amt
          WHEN t.Type = 'Debit' AND tc.Type = 'D+C-' THEN amt
          WHEN t.Type = 'Debit' AND tc.Type = 'C+D-' THEN -amt
       END
    ) as amt
FROM
    tbltran t
    INNER JOIN tbltitles tc ON tc.Title = t.Title
GROUP BY
    t.title;

RESULT

title        amt
-----        ---
Sample1      10
Sample2      220
Sample3      -100

Query 1 SQL Fiddle

Query 2 SQL Fiddle

CASE Documentation

Upvotes: 2

Related Questions