difficult GROUP BY query

[Peter Morris]

I have a TRANSACTIONS table showing activity
in various accounts.

fields include:
- account_id,
- date,
- amount_in
- amount_out
- type

Transaction types might be
- deposit
- withdrawal
- transfer
- charge

I want to show the sum of each transaction type grouped by account_id
I can't work out the syntax for this query, but something like :

SELECT
account_id,
sum (transactions.[amount_in]) as deposit where type = "deposit",
sum (transactions.[amount_out]) as charge where type = "charge",
sum (transactions.[amount_out]) as withdrawal where type =
"withdrawal"
FROM
transactions
GROUP BY
account_id


How do I do this? Is it even possible?

 

[Allen Browne]

Couple of suggestions:

TRANSFORM Sum(CCur(Nz([amount_in],0)-Nz([amount_out],0))) AS Amount
SELECT TRANSACTIONS.account_id
FROM TRANSACTIONS
GROUP BY TRANSACTIONS.account_id
PIVOT TRANSACTIONS.type;

SELECT account_id, type,
sum([amount_in]) AS SumOfAmountIn,
sum([amount_out]) AS SumOfAmountOut
FROM transactions
GROUP BY account_id, type;

Hope one of those is what you need.