case / if / filter / ? in google sheets query

Question

I have a register that has all transactions with their account id. That register has a "Type" Field, on which I want to pull Summary Data.

Transactions:

| A       | B       | C           | D          |
|---------|---------|-------------|------------|
| Account | Amount  | Type        | Date       |
|---------|---------|-------------|------------|
| 1b6f    | 44.21   | Charge      | 2016-09-01 |
| 5g0p    | 101.57  | Charge      | 2016-09-01 |
| 5g0p    | 21.53   | Upgrade Fee | 2016-09-01 |
| 5g0p    | -123.10 | Payment     | 2016-09-07 |
| 1b6f    | 4.43    | Late Fee    | 2016-10-01 |
| 1b6f    | 4.87    | Late Fee    | 2016-11-01 |

I Would like a single query that will allow me to pull all the following summary info

Account
Current Balance
Total Charges
Last Charge Date
First Charge Date
Total Fees
Last Fee Date
Total Payments
Last Payment Date

Something like this:

=QUERY(
    A:D,
    "SELECT A,
     SUM(B),
     SUM(FILTER(D:D,C:C='Charge')),
     MAX(FILTER(D:D,C:C='Charge')),
     MIN(FILTER(D:D,C:C='Charge')),
     SUM(FILTER(B:B,ISNUMBER(FIND('Fee',C)))),
     MAX(FILTER(D:D,ISNUMBER(FIND('Fee',C)))),
     SUM(FILTER(B:B,C:C='Payment')),
     MAX(FILTER(D:D,C:C='Payment'))
     GROUP BY A
     label
     A 'Account',
     SUM(B) 'Current Balance',
     SUM(FILTER(D:D,C:C='Charge')) 'Total Charges',
     MAX(FILTER(D:D,C:C='Charge')) 'Last Charge Date',
     MIN(FILTER(D:D,C:C='Charge')) 'First Charge Date',
     SUM(FILTER(B:B,ISNUMBER(FIND('Fee',C)))) 'Total Fees',
     MAX(FILTER(D:D,ISNUMBER(FIND('Fee',C)))) 'Last Fee Date',
     SUM(FILTER(B:B,C:C='Payment')) 'Total Payments',
     MAX(FILTER(D:D,C:C='Payment')) 'Last Payment Date'
    "
)

With These Results

| Account | Current Balance | Total Charges | Last Charge Date | First Charge Date | Total Fees | Last Fee Date | Total Payments | Last Payment Date |
|---------|-----------------|---------------|------------------|-------------------|------------|---------------|----------------|-------------------|
| 1b6f    | 53.51           | 44.21         | 2016-09-01       | 2016-09-01        | 9.30       | 2016-11-01    | 0              |                   |
| 5g0p    | 121.55          | 223.12        | 2016-10-01       | 2016-09-01        | 21.53      | 2016-09-01    | -123.10        | 2016-09-07        |

Unfortunately, MAX requires input of of a column identifier and it doesn't seem you can use any non scalar functions at all (such as FILTER) or even any non listed aggregate functions (such as JOIN).

I currently am using a bunch of separate queries with different WHERE parameters, however it is very VERY slow.

Answer 1

@trex005 I created table for you

Answer is here: https://docs.google.com/spreadsheets/d/1oC9RYfZD4ITnVfksIVFbC0KkadtFma-JUVk2PdiXqIA/edit?usp=sharing

The sample of the main formula is:

=SUMPRODUCT(FILTER(Sheet1!$B$2:$B,Sheet1!$A$2:$A=$A2,Sheet1!$C$2:$C="Charge"))

It will automatically add a new ID's from Sheet1 to the Sheet2 and count them. I use the same way with google forms to count my pays. Using google forms this way is very effective

The formula for last date of "Charges":

=MAX(FILTER(Sheet1!$D$2:$D,Sheet1!$A$2:$A=$A2,Sheet1!$C$2:$C="Charge"))

where A2:A is

=UNIQUE(Sheet1!A2:A)

Answer 2

Pivot is an option:

=QUERY(A:D,"select A, max(D), min(D), sum(B) where B is not null group by A pivot C",0)

It won't give totals though.

Answer 3

Short answer

In Google Sheets it's not possible to use a unique QUERY to achieve the desired result. One solution is to optimize your set of separate queries

Explanation

As you already mentioned, Google Query Language doesn't support non scalar values as arguments of aggregate functions.

One way to optimize the queries is to use FILTER to remove blank rows from the source array for the queries without a WHERE clause.

Example

=QUERY(
  FILTER(A:D,LEN(A:A),
  "SELECT A, SUM(B) LABEL A 'Current Balance' SUM(B)' Total Charges'"
)

Then you could join the result of all queries by using array sintax

={query1,query2}

Reference

Google spreadsheet "=QUERY" join() equivalent function?