CODEWITHSUNDEEP
sqlms-access
Nick Fleetwood
Nick Fleetwood

Reputation: 531

How to sum a column where another column is equal to other table

I have 2 tables, Assets and Main. I want to create a query that will total the transactions in Main, grouped by each account in Assets. But there's a catch: sometimes the amt needs to be summed as a positive and sometimes as a negative.

In Assets, I have the columns Account and Descript. Account holds "1001", and others, as text; Descript is just text.

Account    Descript
--------------------------
1001       Cash
1101       Receivable

In Main, I have Amt, Ac1, and Ac2.

In Main, when an account is marked in Ac1, the transaction is a positive for that account. When an account is marked in Ac2, the transaction amount is a negative for that account.

Say, for one record, in Main:

  1. You have -1000.00 in "Amt", you have "1001" in "Ac1", and "1101" in "Ac2".
  2. You have 2000.00 in "Amt", you have "1001" in "Ac1", and "1101" in "Ac2".

Data:

Amt    Ac1    Ac2
-------------------
-1000  1001   1101
2000   1001   1101

so then the expected result needs to be:

Account    Descrip    TtlAmt
-------------------------------
1001       Cash        1000.00
1101       Receivable -1000.00

I have some code but I'm not sure if it's helpful.

SELECT 
    Asset.Account, Asset.Descrip AS Expr1, 
    SUM(Main.Amt) AS SumOfAMT, SUM(Main.Amt) AS Expr2
FROM 
    Asset 
LEFT JOIN 
    Main ON (Asset.ACCOUNT = Main.AC2) OR (Asset.ACCOUNT = Main.AC1)
GROUP BY 
    Asset.Account, Asset.Descrip;

Just to be super clear, I also have tables called "Liability", "Expense", etc. But I felt that we can focus on just the one query here, as the rest should fall into place with some guidance.

I know this has nothing to do with problem at hand, but in Excel I use the following formula to accomplish this.

SUM(SUMIF([sum range], [criteria range], [criteria]), SUMIF([sum range], [criteria range], [criteria])*-1)

I thought it may be helpful to explain my end goal.

Upvotes: 1

Views: 1288

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

From a performance perspective, correlated subqueries might be a better approach:

select a.*,
       ( (select nz(sum(m.amt), 0)
          from main as m
          where m.ac1 = a.account
         ) -
         (select nz(sum(m.amt), 0)
          from main as m
          where m.ac2 = a.account
         )
       ) as net_amount             
from assets as a;

In particular, this can take advantage of two indexes: main(ac1, amt) and main(ac2.amt).

In addition, it eliminates the aggregation on the entire result set.

Upvotes: 0

June7
June7

Reputation: 21370

Assuming there is a unique identifier field in Main.

Consider:

Query1

SELECT ID, "Ac1" AS Src, Ac1 AS Act, Amt FROM Main
UNION SELECT ID, "Ac2", Ac2, Amt*-1 FROM Main;

Query2

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN Query1 ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

All in one

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN
(SELECT Ac1 AS Act, Amt FROM Main
UNION SELECT Ac2, Amt*-1 FROM Main) AS Query1 
ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

Upvotes: 2

Related Questions