Is using DISTINCT redundant in a simple aggregate query
Does DISTINCT in simple query with aggregate function?
select DISTINCT salesperson, SUM(sales_amt) from sales GROUP BY salesperson
I understand that there are more complex queries where DISTINCT can have an impact, for example:
select salesperson, SUM(DISTINCT sales_amt) from sales GROUP BY salesperson
(platform support for this syntax may vary)
But I want to confirm that in a simple example query, DISTINCT is redundant.
EDIT: Fixed missing GROUP BY seller
source to share
Assuming you are missing GROUP BY salesperson
(it's not valid in SQL Server if you omit the group) DISTINCT
is redundant in the first query. GROUP BY
performs efficiently DISTINCT
here by aggregating salesperson
.
select DISTINCT salesperson, SUM(sales_amt) from sales GROUP BY salesperson
And you have, as you noticed, fixed that placement DISTINCT
inside an aggregate SUM()
could result in a different set of rows.
source to share
SELECT DISTINCT [Col1], [Col2], ..., [ColN] FROM [Table]
Same as:
SELECT [Col1], [Col2], ..., [ColN] FROM [Table] GROUP BY [Col1], [Col2], ..., [ColN]
DISTINCT
after SELECT
is shorthand for "I want GROUP BY
every column in mine SELECT
. So yeah, I'd say it's redundant if you already need a group anyway (which you do if you have a pivot function)."
source to share