Collections:
Mixing Group Functions with Non-group Selection Fields in SQL Server
Can Group Functions Be Mixed with Non-group Selection Fields in SQL Server?
✍: FYIcenter.com
If a group function is used in the SELECT clause, all other selection fields must be group level fields. Non-group fields can not be mixed with group fields in the SELECT clause. The script below gives you an example of invalid SELECT statements with group and non-group selection fields mixed in a SELECT statement:
SELECT COUNT(*), url FROM fyi_links GO Msg 8120, Level 16, State 1, Line 1 Column 'fyi_links.url' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SELECT 2*COUNT(*), 2*counts FROM fyi_links GO Msg 8120, Level 16, State 1, Line 1 Column 'fyi_links.counts' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
In these examples, COUNT(*) is a group field and "url"/"2*counts" is a non-group field. The error message also tells that "url"/"counts" is not an aggregate function (group function).
⇒ "GROUP BY" - Dividing Query Output into Multiple Groups in SQL Server
⇐ Using Group Functions in the SELECT Clause in SQL Server
⇑ Using SELECT Statements and GROUP BY Clauses in SQL Server
2016-10-25, 1617🔥, 0💬
Popular Posts:
What Are Out-of-Range Errors with DATETIME values in SQL Server Transact-SQL? When you enter DATETIM...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...