Group By VS Distinct

Question

Group By VS Distinct

Anonymous

I'm just wanting some clarification on when to use what with regards to using a GROUP BY VS DISTINCT.

If I want to produce a unique list of elements from a table column, both GROUP BY and SELECT DISTINCT produce the same results. Is one more favored (best practice)?

When is one better/preferred over the other?

Thank you for the clarification.

QuestionBoy

PS: Is there a button, that I've missed somehow, to add DISTINCT to a SELECT statement or is the only method to switch to SQL view and enter it manually? If not, why has MS not added this feature by now?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

They are different. DISTINCT essentially throws away the information from duplicates and returns only one record for each unique set of fields. GROUP BY consolidates the information from all duplicate records into one "group record", so you can (for example) Sum, or Average, or the like.

For example, if you had fields for CustomerID, CustomerName, SaleDate and Amount, a query like

SELECT DISTINCT CustomerID, CustomerName FROM tablename;

you would get one record for each customer... but that's it. It will give one output record for each unique set of those values included in the SELECT clause

A Group By query like

SELECT CustomerID, First(CustomerName), Min(SaleDate), Max(SaleDate), Sum(Amount) FROM tablename GROUP BY CustomerID;

would likewise return one record per customer, but the otherwise concealed fields SaleDate and Amount would be included as aggregates. You must include all the fields that you want to include in the group in the GROUP BY clause, unlike the DISTINCT which automatically includes all the fields in the SELECT.

You can toggle the DISTINCT predicate by viewing the query's Properties - one of them is the "Unique Values" property; setting it to TRUE invokes the DISTINCT feature.

Share via

Group By VS Distinct

0 additional answers