How to Correctly Count Distinct Program Names in SQL Server Using COUNT(DISTINCT)?

Counting Unique Program Names in SQL Server with COUNT(DISTINCT)

This guide demonstrates how to accurately count unique program names in SQL Server using the COUNT(DISTINCT) aggregate function. We'll examine a common query issue and its solution.

The Challenge:

Consider a table named cm_production containing columns like ticket_number, program_type, program_name, and push_number. The goal is to determine the number of distinct program names for each program_type and push_number. An initial attempt might look like this:

<code class="language-sql">DECLARE @push_number INT;
SET @push_number = [HERE_ADD_NUMBER];

SELECT DISTINCT COUNT(*) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE push_number=@push_number 
GROUP BY program_type</code>

This query, however, doesn't provide the correct count of unique program names.

The Solution:

The correct approach involves using COUNT(DISTINCT):

<code class="language-sql">SELECT program_type AS [Type],
       COUNT(DISTINCT program_name) AS [Count]
FROM   cm_production
WHERE  push_number = @push_number
GROUP  BY program_type</code>

Explanation:

COUNT(DISTINCT program_name) counts only the unique, non-null values of program_name within each program_type group. Duplicate program names are disregarded. This yields the accurate count of distinct program names for each program type.

Further Considerations:

The DISTINCT keyword is compatible with various aggregate functions, including SUM(), MIN(), and MAX(). When used with COUNT(), it's functionally equivalent to COUNT(DISTINCT 1) or COUNT(NOT NULL).

The above is the detailed content of How to Correctly Count Distinct Program Names in SQL Server Using COUNT(DISTINCT)?. For more information, please follow other related articles on the PHP Chinese website!