Using DISTINCT in Aggregate Functions

July 5th, 2007 by Sameer | Filed under SQL.

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.

Other Interesting Posts

Share Your Thoughts

Valid XHTML 1.0 Transitional Valid CSS!