Using DISTINCT in Aggregate Functions

By Sameer On July 5, 2007 · Leave a Comment

By Ashiq Alibhai

Note: This article applies to T-SQL; it may or may not work on other varients of SQL.

Many of us are familiar with T-SQL aggregate functions–COUNT, SUM, AVG, MIN, and MAX. However, one often-neglected feature is that you can use these with the DISTINCT keyword.

Imagine you have the following SQL:

CREATE TABLE #TEMP (
  VAL INT)

INSERT INTO #TEMP
VALUES     (1)

INSERT INTO #TEMP
VALUES     (1)

INSERT INTO #TEMP
VALUES     (1)

INSERT INTO #TEMP
VALUES     (2)

INSERT INTO #TEMP
VALUES     (3)

INSERT INTO #TEMP
VALUES     (3)

INSERT INTO #TEMP
VALUES     (7)

SELECT COUNT(VAL),
       COUNT(DISTINCT (VAL))
FROM   #TEMP

…will give you the results 7 and 4. Similarly, SUM(DISTINCT(val)) will give you 13 while the non-distinct gives you 18.

Note that while MIN and MAX are also DISTINCTable, doing so doesn’t really add any additional value.