Differences between MySQL and Oracle GROUP by

This article original for freas_1990, reprint please indicate source: http://blog.csdn.net/freas_1990/article/details/46310145

In Oracle, grouping and aggregation must be paired, and "non-grouped fields" must be aggregated, otherwise execution will be an error. And in MySQL it's completely different.

Mysql> Select Actor.actor_id,actor.first_name from actor join Actor_info on (actor.actor_id=actor_info.actor_id) Group BY actor.first_name;+----------+-------------+| actor_id | First_Name  |+----------+-------------+|       71 | ADAM        | |      165 | AL          | |      173 | ALAN        | |      125 | ALBERT      | |       29 | ALEC        | |       65 | ANGELA      | |       76 | Angelina    | |       49 | ANNE        | |       34 | AUDREY      | |      196 | BELA        | |       83 | BEN         | |        6 | BETTE       |

Select Actor.first_name,count (actor.actor_id) from actor join Actor_info on (actor.actor_id=actor_info.actor_id) group by actor.first_name;+-------------+----------+| First_Name  | count (*) |+-------------+----------+| ADAM        |        2 | | AL          |        1 | | ALAN        |        1 | | ALBERT      |        2 | | ALEC        |        1 | | ANGELA      |        2 | | Angelina    |        1 | | ANNE        |        1 | | AUDREY      |        2 | | BELA        |        1 | | BEN         |        2 |

Where actor_id does not aggregate, but can output values, when a first_name of the group record number is greater than 1, MySQL will output the first record of the group。

Differences between MySQL and Oracle GROUP by