Aggregates (count, sum, avg) in Laravel - Step-by-Step Execution

Concept Flow - Aggregates (count, sum, avg)

Start Query Builder

↓

Select Aggregate Function

↓

Apply Conditions (optional)

↓

Execute Query

↓

Return Aggregate Result

↓

End

This flow shows how Laravel builds and runs an aggregate query like count, sum, or avg, then returns the result.

Execution Sample

Laravel

<?php
$count = DB::table('orders')->count();
$sum = DB::table('orders')->sum('amount');
$avg = DB::table('orders')->avg('amount');

This code counts all orders, sums the 'amount' column, and calculates the average of 'amount' in the 'orders' table.

Execution Table

Step	Query Builder Action	SQL Generated	Result Value	Explanation
1	Start query on 'orders' table	SELECT COUNT(*) FROM orders	null	Prepare to count all rows in 'orders'
2	Execute count()	SELECT COUNT(*) FROM orders	5	Returns total number of rows, here 5 orders
3	Start query on 'orders' table	SELECT SUM(amount) FROM orders	null	Prepare to sum 'amount' column
4	Execute sum('amount')	SELECT SUM(amount) FROM orders	250	Returns sum of 'amount' values, here 250
5	Start query on 'orders' table	SELECT AVG(amount) FROM orders	null	Prepare to average 'amount' column
6	Execute avg('amount')	SELECT AVG(amount) FROM orders	50	Returns average of 'amount' values, here 50
7	End	N/A	N/A	All aggregate queries completed

💡 All aggregate queries executed and results returned

Variable Tracker

Variable	Start	After count()	After sum()	After avg()
$count	null	5	5	5
$sum	null	null	250	250
$avg	null	null	null	50

Key Moments - 2 Insights

Why does count() not need a column name but sum() and avg() do?

count() counts rows, so it works without a column. sum() and avg() need a specific column to calculate totals or averages, as shown in execution_table rows 1-2 vs 3-6.

What happens if the table is empty when calling these aggregates?

count() returns 0 because no rows exist. sum() and avg() return null because there are no values to add or average, similar to the initial 'null' results in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of $sum after step 4?

A 5

B 250

C 50

D null

💡 Hint

Check the 'Result Value' column at step 4 for sum('amount')

At which step does the avg('amount') query execute?

A Step 6

B Step 4

C Step 3

D Step 2

💡 Hint

Look for 'Execute avg('amount')' in the 'Query Builder Action' column

If the 'orders' table had zero rows, what would $count be after step 2?

A 1

B null

C 0

D Error

💡 Hint

Refer to key_moments about empty table behavior and execution_table step 2

Concept Snapshot

Laravel aggregates use query builder methods:
- count() counts rows, no column needed
- sum('column') adds values in a column
- avg('column') calculates average of a column
They generate SQL like SELECT COUNT(*), SUM(col), AVG(col)
Results are returned directly as numbers
Use for quick summary data from tables

Full Transcript

This visual trace shows how Laravel's query builder runs aggregate functions count, sum, and avg on a database table. First, it starts a query on the 'orders' table. Then it builds SQL commands like SELECT COUNT(*), SELECT SUM(amount), and SELECT AVG(amount). Each query runs and returns a number: count returns the number of rows, sum adds all values in the 'amount' column, and avg calculates their average. Variables $count, $sum, and $avg hold these results. Beginners often wonder why count() needs no column but sum() and avg() do; this is because count counts rows, while sum and avg need a specific column to operate on. If the table is empty, count returns 0, but sum and avg return null. This trace helps learners see each step and variable change clearly.

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