Count Case When SQL

Galaxy Glossary

How can I count specific categories using CASE statements in SQL?

The COUNT function combined with CASE WHEN allows you to count rows based on specific conditions. This is useful for categorizing data and analyzing it within different groups.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

The COUNT function in SQL is used to determine the number of rows in a table or a subset of rows that meet specific criteria. When combined with the CASE WHEN statement, you can count rows based on conditions, creating custom categories for analysis. This is particularly useful for analyzing data that has different categories or states. For example, you might want to count the number of orders for different product types or the number of customers in different regions. The CASE WHEN statement allows you to define these categories dynamically within the COUNT function. This approach is more flexible than using multiple COUNT statements for each category, as it allows for a single query to handle multiple conditions. It's also more efficient, as it avoids redundant calculations. The result is a concise and powerful way to aggregate data based on specific conditions.

Why Count Case When SQL is important

This technique is crucial for data analysis and reporting. It allows for a deeper understanding of data by categorizing and counting specific occurrences. This is a fundamental skill for any SQL developer working with data analysis tasks.

Count Case When SQL Example Usage


-- Sample table: Orders
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    OrderStatus VARCHAR(20)
);

-- Sample data (insert into Orders table)
INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate, OrderStatus)
VALUES
(1, 101, 101, '2023-10-26', 'Shipped'),
(2, 102, 102, '2023-10-27', 'Shipped'),
(3, 101, 103, '2023-10-28', 'Pending'),
(4, 103, 101, '2023-10-29', 'Shipped'),
(5, 102, 104, '2023-10-30', 'Cancelled');

-- Query to count orders by status
SELECT
    CASE
        WHEN OrderStatus = 'Shipped' THEN 'Shipped Orders'
        WHEN OrderStatus = 'Pending' THEN 'Pending Orders'
        WHEN OrderStatus = 'Cancelled' THEN 'Cancelled Orders'
        ELSE 'Other Orders'
    END AS OrderCategory,
    COUNT(*) AS OrderCount
FROM
    Orders
GROUP BY
    OrderCategory;

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Common Table Expressions (CTEs) are temporary, named result sets defined within a single SQL statement. They enhance query readability and reusability by breaking down complex queries into smaller, manageable parts.

Where Vs Having SQL

WHERE and HAVING are both used for filtering data in SQL, but they operate on different levels. WHERE filters rows before any aggregation, while HAVING filters groups after aggregation.

Windows Functions In SQL

Window functions in SQL perform calculations over a set of rows related to the current row, unlike aggregate functions which summarize data across all rows. They are powerful tools for analyzing data within a specific context.

Company
BlogFeaturesPricingAboutCareersFeature Requests
Resources
DocsSupportSecurityBrand Kit
Explore
Product TourData Job BoardRecruitersConsultantsStartup ToolsMemes
Learn
Learn SQLBeginner ResourcesData ToolsSQL Interview PrepCommon ErrorsSQL OptimizationSQL In Other LanguagesGlossary
Comparisons
DatagripTablePlusDBeaverBeekeeper StudiopgAdminPosticoCursor
HexBrieferPopSQLSherloqBlazeSQLSweep AIWaii AIOuterbase
© 2025 Galaxy Software Inc.
hello@getgalaxy.io
Reddit IconProduct Hunt IconTwitter / X IconLinkedIn Icon