Can we add `COUNT` and `COUNT(DISTINCT)` aggregations?

[vim89]

Overview

I see we currently have support for sum/min/max/avg and .size for COUNT(1)
We can explicitly add COUNT and COUNT(DISTINCT) helpers as COUNT DISTINCT is common and this gap force users drop to raw SQL.
This addition will reduce friction on grouped selects

Proposal

• Add:
  • On grouped queries: countBy(f: T => Expr[V]), countDistinctBy(f: T => Expr[V])
  • On mapped columns: .count, .countDistinct for any Aggregatable[Expr[T]]
• SQL generated:
  • COUNT(1), COUNT(expr), COUNT(DISTINCT expr) as appropriate

Tests

• Should work across supported dialects (Postgres, MySQL, Sqlite, H2)
• Tests assert both SQL and results

Usage / Examples

• ShoppingCart.select.size - exists
• ShoppingCart.select.countBy(_.productId) returns total items in cart -> COUNT(shoppingCart.product_id)
• ShoppingCart.select.countDistinctBy(_.productId) returns number of distinct items in cart -> COUNT(DISTINCT shoppingCart.product_id)
• On mapped cols - ShoppingCart.select.map(_.productId).count and .countDistinct

Compatibility

• Make purely additive to be backward compatible
• Make generic for all dialects

[vim89]

@lihaoyi, please review & share your thoughts