• Login

DataSoft Corporation

Select Distinct Example

You can use DISTINCT with SUM, AVG, COUNT, MIN, and MAX (but it does not change results with MIN and MAX). DISTINCT eliminates duplicate values before calculating the sum, average or count.

Suppose you want to know the salaries for different departments including the minimum, maximum and salary, and you want to remove duplicate salaries. The following statement would do this, excluding the computer science department:

SELECT dept_name, MIN(salary), MAX(salary), AVG(DISTINCT salary) FROM faculty WHERE dept_name<>'computer science' GROUP BY dept_name

If you wanted to include duplicate salaries, you would use:

SELECT dept_name, MIN(salary), MAX(salary), AVG(salary) FROM faculty WHERE dept_name<>'computer science' GROUP BY dept_name
Friday, 19 April 2019 Posted in Example SQL Statements