Mixing Group Functions with Non-group Selection Fields in SQL Server

Q

Can Group Functions Be Mixed with Non-group Selection Fields in SQL Server?

✍: FYIcenter.com

A

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).

 

Using SELECT Statements and GROUP BY Clauses in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-25, 522👍, 0💬