[sql-server] Using DISTINCT along with GROUP BY in SQL Server

Use DISTINCT to remove duplicate GROUPING SETS from the GROUP BY clause

In a completely silly example using GROUPING SETS() in general (or the special grouping sets ROLLUP() or CUBE() in particular), you could use DISTINCT in order to remove the duplicate values produced by the grouping sets again:

SELECT DISTINCT actors
FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)
GROUP BY CUBE(actors, actors)

With DISTINCT:

actors
------
NULL
a
b

Without DISTINCT:

actors
------
a
b
NULL
a
b
a
b

But why, apart from making an academic point, would you do that?

Use DISTINCT to find unique aggregate function values

In a less far-fetched example, you might be interested in the DISTINCT aggregated values, such as, how many different duplicate numbers of actors are there?

SELECT DISTINCT COUNT(*)
FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)
GROUP BY actors

Answer:

count
-----
2

Use DISTINCT to remove duplicates with more than one GROUP BY column

Another case, of course, is this one:

SELECT DISTINCT actors, COUNT(*)
FROM (VALUES('a', 1), ('a', 1), ('b', 1), ('b', 2)) t(actors, id)
GROUP BY actors, id

With DISTINCT:

actors  count
-------------
a       2
b       1

Without DISTINCT:

actors  count
-------------
a       2
b       1
b       1

For more details, I've written some blog posts, e.g. about GROUPING SETS and how they influence the GROUP BY operation, or about the logical order of SQL operations (as opposed to the lexical order of operations).

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to group-by

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by Count unique values using pandas groupby Pandas group-by and sum Count unique values with pandas per groups Group dataframe and get sum AND count? Error related to only_full_group_by when executing a query in MySql Pandas sum by groupby, but exclude certain columns Using DISTINCT along with GROUP BY in SQL Server Python Pandas : group by in group by and average? How do I create a new column from the output of pandas groupby().sum()?

Examples related to distinct

Using DISTINCT along with GROUP BY in SQL Server How to "select distinct" across multiple data frame columns in pandas? Laravel Eloquent - distinct() and count() not working properly together SQL - select distinct only on one column SQL: Group by minimum value in one field while selecting distinct rows Count distinct value pairs in multiple columns in SQL sql query distinct with Row_Number Eliminating duplicate values based on only one column of the table MongoDB distinct aggregation Pandas count(distinct) equivalent