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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*


+ 5 = 12

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>