Any Reason for Group by Clause Without Aggregation Function

Any reason for GROUP BY clause without aggregation function?

is the GROUP BY statement in any way useful without an accompanying aggregate function?

Using DISTINCT would be a synonym in such a situation, but the reason you'd want/have to define a GROUP BY clause would be in order to be able to define HAVING clause details.

If you need to define a HAVING clause, you have to define a GROUP BY - you can't do it in conjunction with DISTINCT.

PostgreSQL group by without aggregate function. Why does it work?

This is covered, but not especially obvious, in the docs:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

In this case, I'm guessing that id is the primary key of the table user which would make name functionally dependent on id.

Does Group by always need an aggregate function

Does Group by always need an aggregate function?

In an aggregation query, the unaggregated expressions in the SELECT need to be consistent with the expressions in the GROUP BY. All other expressions need to use aggregation functions.

A GROUP BY query does not have to have any aggregation functions in the SELECT.

The most common type of consistency is that the unaggregated expressions are exactly the same. However, the SQL Standard also supports:

• Expressions based on the expressions in the GROUP BY (and constants).
• GROUP BY keys that are missing from the SELECT.
• Any column from a table where the primary key or unique key is in the GROUP BY.

The third involves a concept called "functional dependence" and most databases do not (yet) support this functionality.

If you have a question about how to do something in particular, then ask a new question. Provide sample data, desired results, an explanation of what you want to do, and an appropriate database tag.

Why does MySQL allow group by queries WITHOUT aggregate functions?

I believe that it was to handle the case where grouping by one field would imply other fields are also being grouped:

SELECT user.id, user.name, COUNT(post.*) AS posts 
FROM user 
  LEFT OUTER JOIN post ON post.owner_id=user.id 
GROUP BY user.id

In this case the user.name will always be unique per user.id, so there is convenience in not requiring the user.name in the GROUP BY clause (although, as you say, there is definite scope for problems)

Why do we need GROUP BY with AGGREGATE FUNCTIONS?

It might be easier if you think of GROUP BY as "for each" for the sake of explanation. The query below:

SELECT empid, SUM (MonthlySalary) 
FROM Employee
GROUP BY EmpID

is saying:

"Give me the sum of MonthlySalary's for each empid"

So if your table looked like this:

+-----+------------+
|empid|MontlySalary|
+-----+------------+
|1    |200         |
+-----+------------+
|2    |300         |
+-----+------------+

result:

+-+---+
|1|200|
+-+---+
|2|300|
+-+---+

Sum wouldn't appear to do anything because the sum of one number is that number. On the other hand if it looked like this:

+-----+------------+
|empid|MontlySalary|
+-----+------------+
|1    |200         |
+-----+------------+
|1    |300         |
+-----+------------+
|2    |300         |
+-----+------------+

result:

+-+---+
|1|500|
+-+---+
|2|300|
+-+---+

Then it would because there are two empid 1's to sum together. Not sure if this explanation helps or not, but I hope it makes things a little clearer.

MySQL Aggregate Functions without GROUP BY clause

It's by design - it's one of many extensions to the standard that MySQL permits.

For a query like SELECT name, MAX(age) FROM t; the reference docs says that:

Without GROUP BY, there is a single group and it is indeterminate
which name value to choose for the group

See the documentation on group by handling for more information.

The setting ONLY_FULL_GROUP_BY controls this behavior, see 5.1.7 Server SQL Modes enabling this would disallow a query with an aggregate function lacking a group by statement and it's enabled by default from MySQL version 5.7.5.

Is it possible to use Aggregate function in a Select statment without using Group By clause?

All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY

Good:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3

Also good:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, col5, col6

No other columns = no GROUP BY needed

SELECT MAX(col4)
...

Won't work:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2

Pointless:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, MAX(col4)

Having an aggregate (MAX etc) with other columns without a GROUP BY makes no sense because the query becomes ambiguous.

---

Related Topics