Subscribe RSS FeedBy 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.
