Using DISTINCT in Aggregate Functions

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

• PHP VS. ASP.NET
• Sql Injections and Securing Clipshare Vulnerabilities
• Are You a Sharp Developer?? (Part 1)