Aggregates (count, sum, avg) in Laravel - Mini Project: Build & Apply

Laravel Aggregates: Count, Sum, and Average

📖 Scenario: You are building a simple Laravel application to manage sales data for a small store. You want to calculate some summary statistics like the total number of sales, the total revenue, and the average sale amount.

🎯 Goal: Build a Laravel Eloquent query that uses aggregate functions count(), sum(), and avg() on a Sale model to get the total sales count, total revenue, and average sale amount.

📋 What You'll Learn

Create a Sale model with a price attribute

Define a variable to hold a minimum price filter

Use Eloquent aggregate methods count(), sum(), and avg() with the filter

Store the results in variables named totalSales, totalRevenue, and averageSale

💡 Why This Matters

🌍 Real World

Stores and businesses often need to calculate total sales, revenue, and average sale amounts to understand performance.

💼 Career

Knowing how to use Laravel collections and aggregate functions is essential for backend developers working with data summaries and reports.

1

Create the Sales Data Array

Create a variable called sales as a collection of arrays with these exact entries: ['price' => 100], ['price' => 200], ['price' => 300], ['price' => 400], ['price' => 500].

Laravel

# Create the sales collection with exact prices
# Your code here

Hint

Use collect([]) to create a Laravel collection with the given arrays inside.

2

Set a Minimum Price Filter

Create a variable called minPrice and set it to 200 to filter sales with price greater or equal to this value.

Laravel

$sales = collect([
    ['price' => 100],
    ['price' => 200],
    ['price' => 300],
    ['price' => 400],
    ['price' => 500],
]);

# Define the minimum price filter
# Your code here

Hint

Just create a variable $minPrice and assign the number 200.

3

Filter Sales and Calculate Aggregates

Use the filter() method on $sales to keep only sales with price greater or equal to $minPrice. Then create variables totalSales with the count of filtered sales, totalRevenue with the sum of price, and averageSale with the average price.

Laravel

$sales = collect([
    ['price' => 100],
    ['price' => 200],
    ['price' => 300],
    ['price' => 400],
    ['price' => 500],
]);

$minPrice = 200;

# Filter sales and calculate totalSales, totalRevenue, averageSale
# Your code here

Hint

Use filter() with a callback to keep sales with price >= $minPrice. Then use count(), sum('price'), and avg('price') on the filtered collection.

4

Return the Aggregates in an Array

Create a variable called summary that is an array with keys 'totalSales', 'totalRevenue', and 'averageSale' set to the respective variables.

Laravel

$sales = collect([
    ['price' => 100],
    ['price' => 200],
    ['price' => 300],
    ['price' => 400],
    ['price' => 500],
]);

$minPrice = 200;

$filteredSales = $sales->filter(fn($sale) => $sale['price'] >= $minPrice);

totalSales = $filteredSales->count();
totalRevenue = $filteredSales->sum('price');
averageSale = $filteredSales->avg('price');

# Create summary array with keys totalSales, totalRevenue, averageSale
# Your code here

Hint

Create an associative array $summary with keys matching the variable names and assign the variables as values.

Practice

1. What does the count() method do in Laravel Eloquent?

easy

A. Returns the number of records matching the query

B. Calculates the sum of a column

C. Finds the average value of a column

D. Deletes all records in the table

Solution

1. Step 1: Understand the purpose of count()

   The count() method counts how many records match the query conditions.

2. Step 2: Compare with other aggregate methods

   Unlike sum() or avg(), count() returns a number of rows, not a calculation on column values.

3. Final Answer:

   Returns the number of records matching the query -> Option A

4. Quick Check:

   count() = number of records [OK]

Hint: count() counts rows, sum() and avg() work on columns [OK]

Common Mistakes:

• Confusing count() with sum() or avg()
• Thinking count() returns column values
• Assuming count() deletes records

2. Which of the following is the correct syntax to get the sum of the price column using Eloquent?

easy

A. Product::sum(['price']);

B. Product::sum(price);

C. Product->sum('price');

D. Product::sum('price');

Solution

1. Step 1: Identify correct method call syntax

   In Laravel Eloquent, static calls use double colon :: and column names must be strings in quotes.

2. Step 2: Check each option

   Product::sum('price'); uses Product::sum('price'); which is correct. Product::sum(price); misses quotes, C uses arrow on class, D passes array incorrectly.

3. Final Answer:

   Product::sum('price'); -> Option D

4. Quick Check:

   sum('column') needs quotes and :: [OK]

Hint: Use quotes around column name and :: for static call [OK]

Common Mistakes:

• Omitting quotes around column name
• Using -> instead of :: on model class
• Passing column name as array

3. Given the code:

$total = Order::where('status', 'completed')->sum('amount');

What does $total contain?

medium

A. The count of orders with status 'completed'

B. An error because sum() cannot be used with where()

C. The sum of 'amount' for orders with status 'completed'

D. The average 'amount' for all orders

Solution

1. Step 1: Understand the query filter

   The where('status', 'completed') filters orders to only those completed.

2. Step 2: Apply the sum aggregate

   The sum('amount') adds up the 'amount' values of the filtered orders.

3. Final Answer:

   The sum of 'amount' for orders with status 'completed' -> Option C

4. Quick Check:

   where() filters, sum() adds column values [OK]

Hint: where() filters rows, sum() adds column values of filtered rows [OK]

Common Mistakes:

• Thinking sum() returns count of rows
• Believing sum() cannot be chained with where()
• Confusing sum() with avg()

4. What is wrong with this code snippet?

$average = User::avg(age);

medium

A. avg() requires a second parameter

B. Missing quotes around the column name 'age'

C. avg() cannot be used on User model

D. Using avg() without where() causes error

Solution

1. Step 1: Check method parameter syntax

   The avg() method requires the column name as a string, so it must be quoted.

2. Step 2: Identify the error cause

   Without quotes, PHP treats age as a constant or variable, causing an error or unexpected behavior.

3. Final Answer:

   Missing quotes around the column name 'age' -> Option B

4. Quick Check:

   Column names must be strings in quotes [OK]

Hint: Always quote column names in aggregate methods [OK]

Common Mistakes:

• Omitting quotes around column names
• Thinking avg() needs multiple parameters
• Assuming avg() requires where()

5. You want to get the average price of products that are in stock (stock > 0). Which Eloquent query is correct?

hard

A. Product::where('stock', '>', 0)->avg('price');

B. Product::avg('price')->where('stock', '>', 0);

C. Product::where('stock', '>', 0)->sum('price') / Product::count();

D. Product::sum('price')->where('stock', '>', 0) / Product::count();

Solution

1. Step 1: Filter products with stock > 0

   The where('stock', '>', 0) filters only products that are in stock.

2. Step 2: Calculate average price of filtered products

   The avg('price') calculates the average price of those filtered products.

3. Step 3: Check other options for correctness

   Product::avg('price')->where('stock', '>', 0); calls avg() before where(), which is invalid. Manual calculations with sum divided by count do not filter correctly or mix sum and count improperly.

4. Final Answer:

   Product::where('stock', '>', 0)->avg('price'); -> Option A

5. Quick Check:

   Filter first, then aggregate [OK]

Hint: Chain where() before avg() to filter then calculate [OK]

Common Mistakes:

• Calling aggregate before where()
• Manually dividing sum by count without filtering
• Mixing sum() and avg() incorrectly