Group by clause without aggregate default function in MySQL or MariaDB

Group by clause without aggregate default function in MySQL or MariaDB Default Behaviour

Group by clause is used as a distinct function but it will use the first value which returns in the case of MariaDB or MySQL like the first function.

Example:

Check the country in the group by clause but customer number is not either part of aggregate or group by function but it’s working.

MariaDB [classicmodels]> select country,customerNumber,count(*) from customers_new group by country ;
+--------------+----------------+----------+
| country      | customerNumber | count(*) |
+--------------+----------------+----------+
| Australia    |            114 |        5 |
| Austria      |            382 |        2 |
| Belgium      |            314 |        2 |
| Canada       |            202 |        3 |
| Denmark      |            300 |        2 |

Verified which value is picked by Australia first or max or min. Usually, it picks the first value as seen in the following example:

MariaDB [classicmodels]> select country,customerNumber from customers_new where country='Australia';
+-----------+----------------+
| country   | customerNumber |
+-----------+----------------+
| Australia |            114 |
| Australia |            276 |
| Australia |            282 |
| Australia |            333 |
| Australia |            471 |
+-----------+----------------+
5 rows in set (0.002 sec)

MariaDB [classicmodels]> update customers_new set customernumber = 1000 where customernumber = 114;
Query OK, 1 row affected (0.012 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [classicmodels]> select country,customerNumber from customers_new where country='Australia';
+-----------+----------------+
| country   | customerNumber |
+-----------+----------------+
| Australia |           1000 |
| Australia |            276 |
| Australia |            282 |
| Australia |            333 |
| Australia |            471 |
+-----------+----------------+
5 rows in set (0.001 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.