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

+3


source to share


3 answers


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.

+5


source


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)."

+2


source


None of the queries you have requested will work as you need to have a group in order to return an aggregate (SUM ()). You will need something like this:

SELECT SalesPerson
, SUM(Sales_AMT) AS SalesAmount
FROM Sales 
GROUP BY SalesPerson

      

+1


source







All Articles