Im starting to play around with the QUERY function on Google Sheets and I have 3 Columns with data from different users. Just as follows.
| Users | Amount | Type |
|---|---|---|
| User 1 | 4 | Credit |
| User 2 | 5 | Debit |
| User 3 | 2 | Debit |
| User 2 | 3 | Credit |
| User 3 | 4 | Credit |
| User 1 | 6 | Debit |
I'm trying to use one single QUERY to return a table with all my users grouped showing the total balance where all the credit add up minus all the debits
| Users | Balance |
|---|---|
| User 1 | -2 |
| User 2 | -2 |
| User 3 | -6 |
At the time, I have
SELECT Col1,
Sum(Col2) WHERE Col3 = 'CREDIT',
Sum(Col2) WHERE Col3 = 'DEBIT'
Group by Col1 ")
What of course, is not working. Any idea how to approach this?
I'm currently using one Query for each group then using and SUMIFS with the users to do the math, but definitely would love to know how to do it using one single query
CodePudding user response：
Try the following QUERY() function.
=QUERY({A2:A,INDEX(IF(C2:C="Debit",B2:B*(-1),B2:B))},"select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2) ''")
CodePudding user response：
you can have only one where. instead, you can use and or or but in your case try:
"select Col1,sum(Col2)
where Col3 matches 'CREDIT|DEBIT'
group by Col1"
or:
"select Col1,sum(Col2)
where Col3 = 'CREDIT'
or Col3 = 'DEBIT'
group by Col1"