Group By Vs Distinct Difference In SQL Server

Difference between GROUP BY clause and DISTINCT in sql server. When and where to use GROUP BY and DISTINCT.

Introduction

DISTINCT is used to filter unique records out of the records that satisfy the query criteria. The "GROUP BY" clause is used when you need to group the data and it should be used to apply aggregate operators to each group. Sometimes, people get confused when to use DISTINCT and when and why to use GROUP BY in SQL queries.

Let’s understand how and when to use DISTINCT and GROUP BY.

DISTINCT

When you have a result set containing more than one duplicate records, then you can get unique results out of that by using DISTINCT.

For example, we have Products table and there are some products with its price and get all the data from that which will contain duplication. The right column is having unique records by using DISTINCT keyword.

SELECT Name, Price FROM Products ORDER BY Name

SELECT DISTINCT Name, Price FROM Products ORDER BY Name

GROUP BY

GROUP BY is used when you want to group your data with some criteria or any aggregate function on grouped data. For example -

Again, we are taking same as above table and getting unique records from the result set by using GROUP BY clause.

SELECT Name, Price FROM Products ORDER BY Name

SELECT Name, Price FROM Products GROUP BY Name, Price ORDER BY Name

Now, let’s play with few aggregate functions. Suppose, we need information like product name, price, total available stock quantity, and total available stock Rs.

SELECT Name, Price, COUNT(*) AS TotalQty, SUM(Price) AS TotalStockRs
FROM Products
GROUP BY Price, Name

Which one is more efficient?

It depends on your situation and query. Make a decision of which one is better in your query by checking the execution plans and determine the relative efficiency of queries that generate the same result set.

Summary

Well,  GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.

Thanks for reading. Please comment your queries and feel free to tell me the required changes in this write-up to improve the content quality.