# `fby`

_Apply an aggregate to groups_
```syntax
(aggr;d) fby g
```

Where

- `aggr` is an aggregate function
- `d` and `g` are vectors of the same length (`g` can also be a table, to group on several columns)

collects the items of `d` into sublists according to the corresponding items of `g`, applies `aggr` to each sublist, and returns the results as a vector with the same count as `d`.
If `d` and `g` have different lengths, `fby` signals a `length` error.

> **Tip — When to use `fby`**
>
>
> Use `fby` to collapse cascaded
>
>     select … from select … by … from t
>
> expressions into a single expression:
>
>     select … by … from … where … fby …
>
> Think of `fby` when you find yourself trying to apply a filter to the aggregated column of a table produced by `select … by …`.
>
```q
q)dat:4 9 2 7 0 1 9 2 1 8
q)grp:`a`b`a`b`c`d`c`d`d`a
q)(sum;dat) fby grp
14 16 14 16 9 4 9 4 4 14
```

Collect the items of `dat` into sublists according to the items of `grp`.
```q
q)group grp
a| 0 2 9
b| 1 3
c| 4 6
d| 5 7 8

q)dat group grp
a| 4 2 8
b| 9 7
c| 0 9
d| 1 2 1
```

Apply `aggr` to each sublist.
```q
q)sum each dat group grp
a| 14
b| 16
c| 9
d| 4
```

`fby` builds the result by replacing each item of `grp` with the result of applying `aggr` to its sublist.
```q
q)(sum;dat) fby grp
14 16 14 16 9 4 9 4 4 14
q)(sum each dat group grp)grp / without fby
14 16 14 16 9 4 9 4 4 14
```

If `aggr` returns a list with the same count as its argument, such as [`sums`](sum.md#sums), each item of the result is the corresponding item of that list.
```q
q)(sums;dat) fby grp
4 9 6 16 0 1 9 3 4 14
```

## Vectors
```q
q)dat:2 5 4 1 7             / data
q)grp:"abbac"               / group by
q)(sum;dat) fby grp         / apply sum to the groups
3 9 9 3 7
q)(first;dat) fby grp       / apply first to the groups
2 5 5 2 7
```

## Tables

In a `select`, you usually apply a comparison function to the result of `fby`, for example:
```q
select from t where 10 < (sum;d) fby a
```
```q
q)sp:([]s:`s1`s1`s1`s1`s4`s1`s2`s2`s3`s4`s4`s1;p:`p1`p2`p3`p4`p5`p6`p1`p2`p2`p2`p4`p5;qty:300 200 400 200 100 100 300 400 200 200 300 400)
q)sp
s  p  qty
---------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s4 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
s1 p5 400
```

Sales where the quantity is greater than the average quantity for that part:
```q
q)select from sp where qty > (avg;qty) fby p
s  p  qty
---------
s2 p2 400
s4 p4 300
s1 p5 400
```

Sales where the quantity equals the maximum quantity for that part:
```q
q)select from sp where qty = (max;qty) fby p
s  p  qty
---------
s1 p1 300
s1 p3 400
s1 p6 100
s2 p1 300
s2 p2 400
s4 p4 300
s1 p5 400
```

To group on multiple columns, tabulate them in `g`.
```q
q)update x:0 2 0 1 0 0 0 2 2 2 1 1 from `sp
`sp
q)sp
s  p  qty x
-----------
s1 p1 300 0
s1 p2 200 2
s1 p3 400 0
s1 p4 200 1
s4 p5 100 0
s1 p6 100 0
s2 p1 300 0
s2 p2 400 2
s3 p2 200 2
s4 p2 200 2
s4 p4 300 1
s1 p5 400 1

q)select from sp where qty = (max;qty) fby ([]s;x)
s  p  qty x
-----------
s1 p2 200 2
s1 p3 400 0
s4 p5 100 0
s2 p1 300 0
s2 p2 400 2
s3 p2 200 2
s4 p2 200 2
s4 p4 300 1
s1 p5 400 1
```

The aggregator can be more complex and receive a subset of the table.

For example, suppose each supplier has a limit on the total quantity, and you want all the sales up to the point where the limit is breached.
To find them, compute the cumulative quantity by supplier `s`: the sales whose cumulative quantity is within the limit are valid, and the others are ignored.
The aggregator is a custom function that checks, for each `s`, whether the cumulative quantity is within the limit.
```q
q)update lmt:0.5*sum qty by s from `sp
`sp
q)select from sp where ({exec lmt>=sums qty from x};([]qty;lmt)) fby s
s  p  qty x lmt
---------------
s1 p1 300 0 800
s1 p2 200 2 800
s4 p5 100 0 300
s2 p1 300 0 350
s4 p2 200 2 300
```

---

[q-SQL](qsql.md)
