Aggregate Queries

Writing an aggregate query (SUM, COUNT, AVG) shouldn't be painful. We've tried to take the pain out of this for you.

Rather than drone on, I thought it would be helpful to see our Unit tests. In this way, if you find an issue you can patch us with a Unit test of your own!

At their core, Aggregates are just special Select() queries - and you can use all the same Where/Or/And constraints that you normally would.

All of these queries are using stock Northwind database.

Summing

    double result = new
        Select(Aggregate.Sum("UnitPrice*Quantity", "ProductSales"))
        .From(OrderDetail.Schema)
        .ExecuteScalar<double>();

    result = Math.Round(result, 2);
    Assert.IsTrue(result == 1354458.59);

Averages

    const double expected = 55.5922;

    // overload #1
    double result = new
        Select(Aggregate.Avg("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.Avg(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.Avg("UnitPrice", "AverageUnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.Avg(Product.UnitPriceColumn, "AverageUnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Max

    const double expected = 100.00;

    // overload #1
    double result = new
        Select(Aggregate.Max("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.Max(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.Max("UnitPrice", "MostExpensive"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.Max(Product.UnitPriceColumn, "MostExpensive"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Min

    const double expected = 2.50;

    // overload #1
    double result = new
        Select(Aggregate.Min("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.Min(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.Min("UnitPrice", "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.Min(Product.UnitPriceColumn, "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Standard Deviation

    const double expected = 42.7698669325723;

    // overload #1
    double result = new
        Select(Aggregate.StandardDeviation("UnitPrice"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #2
    result = new
        Select(Aggregate.StandardDeviation(Product.UnitPriceColumn))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #3
    result = new
        Select(Aggregate.StandardDeviation("UnitPrice", "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

    // overload #4
    result = new
        Select(Aggregate.StandardDeviation(Product.UnitPriceColumn, "CheapestProduct"))
        .From(Product.Schema)
        .ExecuteScalar<double>();
    Assert.AreEqual(expected, result);

Average With GroupBy

    int records = new
        Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"))
        .From("Order Details")
        .Where("Quantity").IsEqualTo(120)
        .Where(Aggregate.Avg("UnitPrice"))
        .IsGreaterThan(10)
        .GetRecordCount();

    Assert.AreEqual(5, records);

Average With Simple Where

    int records = new
        Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"))
        .From("Order Details")
        .Where(Aggregate.Avg("UnitPrice"))
        .IsGreaterThan(50)
        .GetRecordCount();

    Assert.AreEqual(7, records);