I see that since I don't know which version of MySQL the group by forces to enter all the fields of the select. (this is configurable, but I have no option to change it). I don't understand why you have to put all the fields of the select in the group by, if I only want to group by the user id for example.
If someone is kind enough to explain (for fools, with apples) the reason for this, I would be eternally grateful... I'm looking on the internet, and I don't understand the reasons they give. They say they are good practices, but until recently it could be done without problems and now... :(
It's because mysql was actually doing it wrong from the start .
The clauses
group by
, in what is called ANSI SQL (that is standard sql, which is the same for all databases), must yes or yes, have all the fields that are inselect
it and that are not used in a grouping function, defined ingroup by
it to know how it has to be grouped, in what order. The grouping order is given by thegroup by
, not theselect
.The problem with not putting some fields in the
group by
, is: How does the database know, which of all the data it has in its database, it has to put in that field?Let's see it with an example.
given a table
Let's suppose that we want to obtain the sales by category.
This query is perfectly valid in mysql (and as a side note, in ACCESS as well), and gives us the following result:
However, what are you going to put in the category name? which of all the data in the rows does it put?
No one can be sure what data goes there... In this case, categoryname has the same thing in all the rows. But what happened, if over time the category changed its name, but not its code? Which one will you bring?
Let's look at it another way... let's "break" the table above:
and let's run the same
select
And now... how would the result be?
Or the next?
Both results are correct. And that's why the database can't assume what you want to put in categoryname, and that's why all the fields must be in the
group by
, or be part of an aggregation function.As a note, an aggregation function is one that analyzes the fields, and returns a result from them (sum, count, average, etc.)