CODEWITHSUNDEEP
sqlms-accessms-access-2010
ashan
ashan

Reputation: 57

Subtract two columns

I have the following MS Access table:

itemid   qty  flag
1         2    0
1         1    1 
2         5    0
2         4    1

I want to write a query to get the balance (qty-qty) and group by flag.

As an example:

(sum of qty where flag =0) - (sum of qty where flag =1)

My final output should be:

1=1 
2=1

Upvotes: 2

Views: 995

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

Use conditional aggregation:

SELECT itemid,
       NZ(SUM(IIF(flag = 0, qty, 0)), 0) - NZ(SUM(IIF(flag = 1, qty, 0)), 0) AS difference
FROM yourTable
GROUP BY itemid

Upvotes: 2

Related Questions