EF Core - Aggregate SELECT queries

I'll cover three SQL aggregate functions: COUNT, SUM, and AVERAGE.

For each example, I’ll show the LINQ query, the SQL query it generates, and the results of executing it.

Books Table Sample Data

For these examples, I’ll use the following set of book data:

SELECT COUNT()

Total Count

Select the total number of books:

using (var context = new BookStoreContext(connectionString))
{
    var count = await context.Books.CountAsync();

    Console.WriteLine($"There are {count} books.");
}

This generates the following SQL query:

SELECT COUNT(*)
FROM [Books] AS [b]

Executing this outputs:

There are 8 books

Count Per Group

Select the number of books published per year:

using (var context = new BookStoreContext(connectionString))
{
    var countPerGroup = await context.Books
        .GroupBy(b => b.YearOfPublication)
        .Select(bookGroup => new { Year = bookGroup.Key, Count = bookGroup.Count() })
        .ToListAsync();

    foreach(var bookGroup in countPerGroup)
    {
        Console.WriteLine($"Year {bookGroup.Year} has {bookGroup.Count} book(s).");
    }
}

This generates the following SQL query with a GROUP BY:

SELECT [b].[YearOfPublication] AS [Year], COUNT(*) AS [Count]
FROM [Books] AS [b]
GROUP BY [b].[YearOfPublication]

Executing this outputs:

Year 1813 has 1 book(s).
Year 1851 has 1 book(s).
Year 1925 has 1 book(s).
Year 1949 has 1 book(s).
Year 1951 has 1 book(s).
Year 1960 has 1 book(s).
Year 1866 has 1 book(s).
Year 1869 has 1 book(s).

Count Per Group Having Condition

Select the number of books per year, but only include years with more than one book published:

using (var context = new BookStoreContext(connectionString))
{
    var countPerGroup = await context.Books
        .GroupBy(b => b.YearOfPublication)
        .Select(bookGroup => new { Year = bookGroup.Key, Count = bookGroup.Count() })
        .Where(bookGroup => bookGroup.Count > 1)
        .ToListAsync();

    foreach (var bookGroup in countPerGroup)
    {
        Console.WriteLine($"Year {bookGroup.Year} has {bookGroup.Count} book(s).");
    }
}

This generates the following SQL query with a GROUP BY HAVING:

SELECT [b].[YearOfPublication] AS [Year], COUNT(*) AS [Count]
FROM [Books] AS [b]
GROUP BY [b].[YearOfPublication]
HAVING COUNT(*) > 1

SELECT SUM()

Total Sum

Select the total sales revenue for all books:

using (var context = new BookStoreContext(connectionString))
{
    var totalRevenue = await context.Books.SumAsync(b => b.SalesRevenue);

    Console.WriteLine($"The books generated {totalRevenue:C} total revenue.");
}

This generates the following SQL query:

SELECT COALESCE(SUM([b].[SalesRevenue]), 0.0)
FROM [Books] AS [b]

Executing this outputs:

The books generated $107,000,000.00 total revenue.

Sum Per Group

Select the total sales revenue per year:

using (var context = new BookStoreContext(connectionString))
{
    var sumPerGroup = await context.Books
        .GroupBy(b => b.YearOfPublication)
        .Select(bookGroup => new
        { 
            Year = bookGroup.Key, 
            TotalRevenue = bookGroup.Sum(b => b.SalesRevenue)
        })
        .ToListAsync();

    foreach (var bookGroup in sumPerGroup)
    {
        Console.WriteLine($"Books in year {bookGroup.Year} generated {bookGroup.TotalRevenue:C} in sales.");
    }
}

This generates the following SQL query with a GROUP BY:

SELECT [b].[YearOfPublication] AS [Year], COALESCE(SUM([b].[SalesRevenue]), 0.0) AS [TotalRevenue]
FROM [Books] AS [b]
GROUP BY [b].[YearOfPublication]

Executing this outputs:

Books in year 1813 generated $25,000,000.00 in sales.
Books in year 1851 generated $5,000,000.00 in sales.
Books in year 1925 generated $20,000,000.00 in sales.
Books in year 1949 generated $8,000,000.00 in sales.
Books in year 1951 generated $10,000,000.00 in sales.
Books in year 1960 generated $15,000,000.00 in sales.
Books in year 1866 generated $7,000,000.00 in sales.
Books in year 1869 generated $12,000,000.00 in sales.

Sum Per Group Having Condition

Select total sales revenue per year, excluding years with less than $15 million in revenue:

using (var context = new BookStoreContext(connectionString))
{
    var sumPerGroup = await context.Books
        .GroupBy(b => b.YearOfPublication)
        .Select(bookGroup => new
        {
            Year = bookGroup.Key,
            TotalRevenue = bookGroup.Sum(b => b.SalesRevenue)
        })
        .Where(bookGroup => bookGroup.TotalRevenue >= 15_000_000)
        .ToListAsync();

    foreach (var bookGroup in sumPerGroup)
    {
        Console.WriteLine($"Books in year {bookGroup.Year} generated {bookGroup.TotalRevenue:C} in sales.");
    }
}

This generates the following SQL query with a GROUP BY HAVING:

SELECT [b].[YearOfPublication] AS [Year], COALESCE(SUM([b].[SalesRevenue]), 0.0) AS [TotalRevenue]
FROM [Books] AS [b]
GROUP BY [b].[YearOfPublication]
HAVING COALESCE(SUM([b].[SalesRevenue]), 0.0) >= 15000000.0

Executing this outputs:

Books in year 1925 generated $20,000,000.00 in sales.
Books in year 1960 generated $15,000,000.00 in sales.

SELECT AVG()

Total Average

Select the average sales revenue for all books:

using (var context = new BookStoreContext(connectionString))
{
    var averageRevenue = await context.Books.AverageAsync(b => b.SalesRevenue);

    Console.WriteLine($"The average sales revenue for the books is {averageRevenue:C}");
}

This generates the following SQL query:

SELECT AVG([b].[SalesRevenue])
FROM [Books] AS [b]

Executing this outputs:

The average sales revenue for the books is $13,375,000.00

Average Per Group

Select the average sales revenue per year:

using (var context = new BookStoreContext(connectionString))
{
    var averagePerGroup = await context.Books
        .GroupBy(b => b.YearOfPublication)
        .Select(bookGroup => new
        {
            Year = bookGroup.Key,
            AverageRevenue = bookGroup.Average(b => b.SalesRevenue)
        })
        .ToListAsync();

    foreach (var bookGroup in averagePerGroup)
    {
        Console.WriteLine($"Books in year {bookGroup.Year} had an average sales revenue of {bookGroup.AverageRevenue:C}");
    }
}

This generates the following SQL query with a GROUP BY:

SELECT [b].[YearOfPublication] AS [Year], AVG([b].[SalesRevenue]) AS [AverageRevenue]
FROM [Books] AS [b]
GROUP BY [b].[YearOfPublication]

Executing this outputs:

Books in year 1813 had an average sales revenue of $25,000,000.00
Books in year 1851 had an average sales revenue of $5,000,000.00
Books in year 1925 had an average sales revenue of $20,000,000.00
Books in year 1949 had an average sales revenue of $8,000,000.00
Books in year 1951 had an average sales revenue of $10,000,000.00
Books in year 1960 had an average sales revenue of $15,000,000.00
Books in year 1866 had an average sales revenue of $7,000,000.00
Books in year 1869 had an average sales revenue of $12,000,000.00

SELECT AVG(), COUNT(), and SUM()

If you want to include multiple aggregates, you can combine them into one query. For example, select the average, total sales revenue, and the count of books per year:

using (var context = new BookStoreContext(connectionString))
{
    var statsPerGroup = await context.Books
        .GroupBy(b => b.YearOfPublication)
        .Select(bookGroup => new
        {
            Year = bookGroup.Key,
            AverageRevenue = bookGroup.Average(b => b.SalesRevenue),
            TotalRevenue = bookGroup.Sum(b => b.SalesRevenue),
            Count = bookGroup.Count()
        })
        .ToListAsync();

    foreach (var bookGroup in statsPerGroup)
    {
        Console.WriteLine($"In year {bookGroup.Year}, there were {bookGroup.Count} books. They had an average sales revenue of {bookGroup.AverageRevenue:C} and total sales of {bookGroup.TotalRevenue:C}");
    }
}

This generates the following SQL query with all three aggregate functions:

SELECT [b].[YearOfPublication] AS [Year], AVG([b].[SalesRevenue]) AS [AverageRevenue], COALESCE(SUM([b].[SalesRevenue]), 0.0) AS [TotalRevenue], COUNT(*) AS [Count]
FROM [Books] AS [b]
GROUP BY [b].[YearOfPublication]

Executing this outputs:

In year 1813, there was 1 book. It had an average sales revenue of $25,000,000.00 and total sales of $25,000,000.00
In year 1851, there was 1 book. It had an average sales revenue of $5,000,000.00 and total sales of $5,000,000.00
In year 1925, there was 1 book. It had an average sales revenue of $20,000,000.00 and total sales of $20,000,000.00
In year 1949, there was 1 book. It had an average sales revenue of $8,000,000.00 and total sales of $8,000,000.00
In year 1951, there was 1 book. It had an average sales revenue of $10,000,000.00 and total sales of $10,000,000.00
In year 1960, there was 1 book. It had an average sales revenue of $15,000,000.00 and total sales of $15,000,000.00
In year 1866, there was 1 book. It had an average sales revenue of $7,000,000.00 and total sales of $7,000,000.00
In year 1869, there was 1 book. It had an average sales revenue of $12,000,000.00 and total sales of $12,000,000.00

This example demonstrates how you can use EF Core to aggregate and filter data using different aggregate functions in a book-related context.