I know that I can not have clauses in the select that do not appear in Group By. But is it possible otherwise? For example:
Select B
From Table
Group By A,B
SQL: Can I have clauses in GroupBy that do not appear in Select?
1
asked by anonymous 10.05.2015 / 03:36
1 answer
2
I will respond to the two DBMSs in the question tags:
Group By joins a set of records and produces a summary (a single record) for each of the identified groups. Groups are identified based on one or more columns or expressions included in the Group By clause.
Both MySql and Oracle allow your SELECT clause to omit columns included in Group By. For example, this statement will work in both DBMSs.
SELECT coluna1, COUNT(1)
FROM tbl_tabela
GROUP BY coluna1, coluna2
(The usefulness of an expression like the one above can be questioned, of course, since you lose essential information to identify the group created for each of the lines in the result.)
To complete, and because it is the DBMS that I have been using lately, the Sql Server, according to the information in manual , also allows Group By expressions to contain table columns, derived tables, FROM clause views. However, Group By columns need not appear in the SELECT clause.
One last comment regarding your question, MySql allows to include columns in your SELECT clause that are not in GROUP BY. More info on the manual page
10.05.2015 / 10:56