Can Group Functions Be Mixed with Non-group Selection Fields in MS SQL Server?
Submitted by: AdministratorIf 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 ggl_links
GO
Msg 8120, Level 16, State 1, Line 1
Column 'ggl_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 ggl_links
GO
Msg 8120, Level 16, State 1, Line 1
Column 'ggl_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).
Submitted by: Administrator
SELECT COUNT(*), url FROM ggl_links
GO
Msg 8120, Level 16, State 1, Line 1
Column 'ggl_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 ggl_links
GO
Msg 8120, Level 16, State 1, Line 1
Column 'ggl_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).
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Start SQL Server Browser Service? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | How to Create a View using Data from Another View? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Configure ODBC DSN with Different Port Numbers? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |