Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Tally Count

Tally Count

Learn about the Tally count pattern to identify value frequencies in a dataset, helping with insights and decision-making through SQL aggregation.

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. 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.

Press + to interact
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.

Press + to interact
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.

Press + to interact
SELECT   OrderDate,
         COUNT(*) AS OrdersPerDay
FROM     Orders
GROUP BY OrderDate
ORDER BY OrderDate;

4. Count ...