Search⌘ K
AI Features
Home
Courses
Grokking the SQL Interview Patterns

Tally Count

Explore the tally count SQL pattern to group and count data occurrences, helping you analyze frequencies such as orders per customer or products per category. Understand how to apply COUNT and GROUP BY effectively for data-driven decisions across various real-world use cases.

We'll cover the following...

Imagine we are running a fast-growing online store and want to understand which products are being ordered the most.

Are customers ordering more electronics or home goods? Which specific items are popular? To answer these questions, we need to count how often items appear in our data. This is where the Tally count pattern comes in. By utilizing SQL COUNT alongside GROUP BY SQL techniques; it helps us quickly determine frequency, identify trends, and make data-driven decisions.

In this lesson, we will:

  • Learn what the Tally count pattern is and why it’s useful.

  • Understand the structure and logic behind tallying values in SQL.

  • Explore variations in difficulty, from basic group counts to more advanced filtering.

  • Apply the pattern through exercises and real-world use cases.

Pattern overview

Let’s dive into how we can turn raw data into insights by simply counting what shows up.

Category:

  • Aggregation patterns

Intent:

To count the number of times a value or combination of values appears in a dataset.

Motivation:

We often need to understand how often things happen. Whether it's counting how many orders a customer placed, how many products were sold, or how frequently each category is purchased, these counts are key to analysis, reporting, and decision-making.

Also known as:

  • Frequency count

  • Count by group

  • Count occurrences

Structure

SELECT    column_to_group,
          COUNT(*) AS tally_count
FROM      table_name
[WHERE    condition]
GROUP BY  column_to_group
[ORDER BY tally_count DESC];
Syntax for Tally count pattern

Keywords

GROUP BY, COUNT(*), ORDER BY, AS

Problem structure

We use the Tally count pattern when:

  • We want to group records based on one or more columns.

  • Then, we count how many times each group appears.

  • Optionally, we can sort by the count to highlight the most or least frequent values.

  • Look for keywords like: “how many times,” “count of,” “frequency of,” “number of occurrences,” or “most/least common” to identify when this pattern applies.

Example use cases

1. Count how many orders each customer placed.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

Write an SQL query to find the total number of orders placed by each customer. The output should include:

  • CustomerID

  • TotalOrders: The number of orders they placed

Sort the results by TotalOrders in descending order, so the most active customers appear first.

MySQL
SELECT   CustomerID,
         COUNT(*) AS TotalOrders
FROM     Orders
GROUP BY CustomerID
ORDER BY TotalOrders DESC;

2. Find the number of times each product appears in orders.

Given the following structure of the Order_Details table:

Field

Type

OrderDetailID

int

OrderID

int

ProductID

int

Quantity

int

TotalItemPrice

decimal(10,2)

Where OrderDetailID is the primary key, OrderID is foreign key from Order table, and ProductID is foreign key from Products table. The table contains information about orders. Each row in the table represents a product included in a specific order (i.e., if a product appears multiple times, it has been ordered multiple times across different orders).

Write an SQL query to find out how frequently each product has been ordered. The output should include:

  • ProductID

  • TimesOrdered: How many times does the product appear in orders

Sort the results by TimesOrdered in descending order to show the most frequently ordered products first.

MySQL
SELECT   ProductID,
         COUNT(*) AS TimesOrdered
FROM     Order_Details
GROUP BY ProductID
ORDER BY TimesOrdered DESC;

3. Count the number of orders per day.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed. Your task is to write an SQL query that calculates the total number of orders placed on each day.

The output should include:

  • OrderDate

  • OrdersPerDay: The number of orders placed on that date

Sort the results by OrderDate in ascending order so that trends over time can be easily observed.

MySQL
SELECT   OrderDate,
         COUNT(*) AS OrdersPerDay
FROM     Orders
GROUP BY OrderDate
ORDER BY OrderDate;

4. Count customers by tier.

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table contains information about customers. The CustomerTier categorizes customers as 'New', 'Regular', or 'VIP' based on their purchase activity and engagement. Write an SQL query that calculates how many customers belong to each tier.

The output should include:

  • CustomerTier

  • NumCustomers: The number of customers in that tier

MySQL
SELECT   CustomerTier,
         COUNT(*) AS NumCustomers
FROM     Customers
GROUP BY CustomerTier;

5. Count the products available in each category.

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.

Also, the following structure of Categories table:

Field

Type

CategoryID

int

CategoryName

varchar(50)

Where CategoryID is the primary key. The table contains information about categories of products. Your company sells products across various categories, tracked in the Products and Categories tables. Write an SQL query to determine how many products exist in each category.

The result should include:

  • CategoryName

  • ProductsInCategory: The total number of products assigned to that category

Sort the output by ProductsInCategory in descending order to highlight the most populated categories first.

MySQL
SELECT   c.CategoryName,
         COUNT(p.ProductID) AS ProductsInCategory
FROM     Products p
JOIN     Categories c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryName
ORDER BY ProductsInCategory DESC;

Related patterns

  • Group buckets: Group and summarize values using ranges or categories.

  • Rolling totals: Compute cumulative counts or moving averages over time.

  • Existence check: When we want to count only if something exists.

  • Deduplication: Sometimes combined with counting unique entries.

Practice exercises

Easy

1. Count the number of customers per tier.

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table contains information about customers. In the table, each customer is assigned a CustomerTier based on their activity level. The possible tiers are 'New', 'Regular', and 'VIP'.

Write an SQL query to count how many customers belong to each tier. The output should include:

  • CustomerTier

  • Total: The number of customers in that tier

Sample output:

CustomerTier Total
Regular 12
VIP 8
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

2. Count how many orders exist per delivery status.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed. Write an SQL query to find out how many orders fall under each delivery status.

The output should include:

  • DeliveryStatus

  • OrderCount: The number of orders for each status

Sample output:

DeliveryStatus OrderCount
Shipped 12
Delivered 33
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

3. Count the number of products for each category.

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products. You are analyzing product distribution in the Products table. Each product is assigned to a category using the CategoryID field.

Write an SQL query that returns the number of products in each category. The output should include:

  • CategoryID

  • ProductCount: The total number of products associated with that category

Sample output:

CategoryID ProductCount
1 7
2 3
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

4. Count the number of suppliers per product.

Given the following structure of the Product_Suppliers table:

Field

Type

ProductID

int

SupplierID

int

Where ProductID is the foreign key from Products table and SupplierID is the foreign key from the Suppliers table. The table contains information about suppliers and their products. The Product_Suppliers table represents a many-to-many relationship between products and their suppliers. Each row links a ProductID with a SupplierID.

Write an SQL query to find out how many suppliers are associated with each product. The output should include:

  • ProductID

  • SupplierCount—the number of suppliers that supply the product

Sample output:

ProductID SupplierCount
1 2
2 2
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

5. Count how many customers were referred by each existing customer.

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table contains information about customers. The table includes a ReferralID column, which indicates which existing customer referred a new customer. A NULL value means the customer was not referred by anyone.

Write an SQL query to find out how many referrals each customer has made. The output should include:

  • ReferralID: The CustomerID of the referring customer

  • Referrals: The number of customers they referred

Only include rows where a referral exists (i.e., ReferralID is not null).

Sample output:

ReferralID Referrals
1 5
2 5
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

Medium

1. Count how many VIP customers placed at least one order.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

Also, the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table contains information about customers.

The Customers and Orders tables track user information and their corresponding orders. Each customer is assigned a tier in the CustomerTier column ('New', 'Regular', 'VIP', etc).

Write an SQL query to find out how many orders each VIP customer has placed. The output should include:

  • CustomerID: The ID of the VIP customer

  • NumOrders: The total number of orders they have placed

Only include customers whose CustomerTier is 'VIP'.

Sample output:

CustomerID NumOrders
2 3
7 2
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

2. Count how many products have more than 10 monthly sales.

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products. In the table, each product has a MonthlySales value that tracks how many units are sold per month.

Write an SQL query to count how many products have more than 10 units sold per month. The output should include:

  • HighSalesProducts: The number of products where MonthlySales is greater than 10.

Sample output:

HighSalesProducts
6
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

3. Count orders placed using each payment method.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

In the Orders table, each order includes a PaymentMethod field indicating how the customer paid. Possible values include 'Credit Card', 'PayPal', and 'Cash on Delivery'.

Write an SQL query to count how many orders were placed using each payment method. The output should include:

  • PaymentMethod

  • NumOrders – the number of orders using that method

Sample output:

PaymentMethod NumOrders
Credit Card 32
PayPal 16
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

4. Count how many products were ordered more than once.

Given the following structure of the Order_Details table:

Field

Type

OrderDetailID

int

OrderID

int

ProductID

int

Quantity

int

TotalItemPrice

decimal(10,2)

Where OrderDetailID is the primary key, OrderID is foreign key from Order table, and ProductID is foreign key from Products table. The table contains information about orders. In the Order_Details table, each row represents a product included in an order. Some products appear in multiple orders.

Write an SQL query to find all products that have been ordered more than once. The output should include:

  • ProductID

  • TimesOrdered – how many times has the product appeared in orders

Only include products that were ordered more than once.

Sample output:

ProductID TimesOrdered
1 3
2 3
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

5. Count the number of orders that were returned (ReturnCount > 0).

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

The Orders table tracks each customer order, including a ReturnCount column that indicates how many times an order has been returned (if at all).

Write an SQL query to count the number of orders that have at least one return (ReturnCount > 0). The output should include:

  • ReturnedOrders: The total number of orders with returns

Sample output:

ReturnedOrders
2
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

Hard

1. Top 5 most ordered products by total quantity.

Given the following structure of the Order_Details table:

Field

Type

OrderDetailID

int

OrderID

int

ProductID

int

Quantity

int

TotalItemPrice

decimal(10,2)

Where OrderDetailID is the primary key, OrderID is a foreign key from Order table, and ProductID is foreign key from Products table. The table contains information about orders.

Also, the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.

Write an SQL query to find the top 5 best-selling products based on the total quantity ordered across all orders. The output should include:

  • ProductName

  • TotalQuantity: The sum of units sold for each product.

Sort the results in descending order by TotalQuantity and return only the top 5 products.

Sample output:

ProductName TotalQuantity
Novel B 8
Novel A 8
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

2. Count of distinct products purchased by each customer.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

Also, the following structure of the Order_Details table:

Field

Type

OrderDetailID

int

OrderID

int

ProductID

int

Quantity

int

TotalItemPrice

decimal(10,2)

Where OrderDetailID is the primary key, OrderID is foreign key from Order table, and ProductID is foreign key from Products table. The table contains information about orders.

Write an SQL query to find out how many unique products each customer has ordered across all their orders. You can achieve this using a count distinct SQL operation. The output should include:

  • CustomerID

  • UniqueProducts: The count of distinct products ordered by that customer.

Sample output:

CustomerID UniqueProducts
1 4
2 2
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

3. Count orders per customer for those with more than 2 orders.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

In the Orders table, each row represents a purchase made by a customer. You are interested in identifying the most engaged customers based on order activity.

Write an SQL query to find customers who have placed more than 2 orders. The output should include:

  • CustomerID

  • TotalOrders: The number of orders placed by that customer.

Only include customers whose total number of orders is greater than 2.

Sample output:

CustomerID TotalOrders
1 6
2 3
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

4. Count how many late deliveries each employee is responsible for.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

In the Orders table, each order is optionally assigned to an employee (EmployeeID) responsible for processing or managing the delivery. The table also includes a LateDelivery field, which is a boolean indicating whether the order was delivered later than expected.

Write an SQL query to find out how many late deliveries are associated with each employee. Only include:

  • Orders where LateDelivery = TRUE

  • Orders with a non-null EmployeeID

The output should include:

  • EmployeeID

  • LateDeliveries: The number of late deliveries they are responsible for.

Sample output:

EmployeeID LateDeliveries
3 3
4 2
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

5. Count customers who haven’t made any purchases.

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table tracks all registered users, including their most recent purchase in the LastPurchaseDate column. A NULL value in this column indicates that the customer has never made a purchase.

Write an SQL query to count how many customers have never made a purchase. The output should include:

  • InactiveCustomers: The number of customers with LastPurchaseDate as NULL

Sample output:

InactiveCustomers
11
MySQL
-- Write your query here

If you’re stuck, click the “Show Solution” button.

By now, we’ve explored how to use the Tally count pattern to answer questions about frequency and occurrences in a dataset.

We learned how to count grouped data, apply filters, and combine counts with joins for deeper insights. Whether we’re tallying customers by tier, orders per day, or product popularity, this pattern gives us a simple yet powerful way to summarize and explore data.

Keep practicing these tallying techniques!

They form the foundation of many real-world queries and are often the first step in solving bigger, more complex problems. You're building strong SQL intuition, keep going!