Is usage of DISTINCT redundant in a simple query with an aggregate function
oracle, sql, sql-server
Solution
Assuming you are missing `GROUP BY salesperson` (it's invalid in SQL Server if you omit the group by), the `DISTINCT` is redundant in your first query. The `GROUP BY` effectively performs a `DISTINCT` here by aggregating `salesperson`.
select DISTINCT salesperson, SUM(sales_amt) from sales GROUP BY salesperson
And you are, as you've noted, correct that placement of the `DISTINCT` inside the aggregate `SUM()` may produce a different rowset.
Problem
Does DISTINCT in a simple query with an aggregate function have any effect? ``` select DISTINCT salesperson, SUM(sales_amt) from sales GROUP BY salesperson ``` I realize there are more complicated queries where DISTINCT can have an affect, such as: ``` select salesperson, SUM(DISTINCT sales_amt) from sales GROUP BY salesperson ``` (support for that syntax by platform may vary) But I want to confirm that in the simple query example, DISTINCT is redundant. EDIT: fixed missing GROUP BY salesperson