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;

Count Case When SQL Syntax

Common Mistakes

Frequently Asked Questions (FAQs)

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Company
BlogFeaturesPricingAboutCareersFeature Requests
Resources
Resource LibraryDocsSupportBrand Kit
Explore
Product TourData Job BoardRecruitersConsultantsStartup ToolsMemes
Learn
Learn SQLBeginner ResourcesData ToolsSQL Interview PrepCommon ErrorsSQL OptimizationSQL In Other LanguagesGlossary
Comparisons
DatagripTablePlusDBeaverBeekeeper StudiopgAdminMySQL WorkbenchSQL Server Management StudioPosticoDbVisualizerNao LabsCursor
HexBrieferPopSQLSherloqBlazeSQLSweep AIWaii AIOuterbase
© 2025 Galaxy Software Inc.
hello@getgalaxy.io
Terms of ServicePrivacy PolicySecurity
Reddit IconProduct Hunt IconTwitter / X IconLinkedIn Icon