Menu
is GROUP BY used only with aggregate functions ?
Can anyone give an example where it is used not in conjunction with aggregate functions.
702
(3) GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN. If it is used with single row functions,Oracle throws an exception as "ORA-00979: not a GROUP BY expression". (4) Aggregate functions cannot be used in a GROUP BY clause.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
GROUP BY without Aggregate Functions Although most of the times GROUP BY is used along with aggregate functions, it can still still used without aggregate functions — to find unique records.
Group by is one of the most frequently used SQL clauses. It allows you to collapse a field into its distinct values. This clause is most often used with aggregations to show one value per grouped field or combination of fields. We can use an SQL group by and aggregates to collect multiple types of information.
SELECT with GROUP BY can be used as an alternative to SELECT DISTINCT. Pranay Rana's example is essentially equivalent to
SELECT DISTINCT UserName, Departmentid FROM user
If you want an example where the preference of GROUP BY over DISTINCT is justified, here's one. Say, you want to return items that only occur once in a table:
SELECT Item
FROM atable
GROUP BY Item
HAVING COUNT(*) = 1
As you can see, the query only returns a non-aggregated column.
It still uses an aggregate function, though, even if not in the SELECT clause. There may be situations where you must prefer GROUP BY to DISTINCT while not needing to use aggregate functions at all, but I'll have to be platform specific at this point.
In particular, if you are using SQL Server, you will find a difference between the results of the following two statements:
#1:
WITH data (f) AS (
SELECT 'abc' UNION ALL
SELECT 'def' UNION ALL
SELECT 'abc'
)
SELECT f, ROW_NUMBER() OVER (ORDER BY f) FROM data GROUP BY f
#2:
WITH data (f) AS (
SELECT 'abc' UNION ALL
SELECT 'def' UNION ALL
SELECT 'abc'
)
SELECT DISTINCT f, ROW_NUMBER() OVER (ORDER BY f) FROM data
(If you aren't, you can still see the results for yourself using Stack Exchange Data Explorer.)
Both statements are meant to return distinct items ranked. However, only the first statement (with GROUP BY) returns the results as expected, while the latter (with DISTINCT) returns all items. Obviously, ranking functions in SQL Server are evaluated after GROUP BY, but before DISTINCT The reason is, the SELECT clause in SQL Server is evaluated after GROUP BY, but before DISTINCT, which makes you prefer GROUP BY over DISTINCT in this case. (Thanks @ypercube for nudging me in the right direction.)
180
Something like this where I have not applied any aggregate function
Select City,State from geodata group by City,State
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
