Hello everyone. It’s Ray! I shared how to import Chinese data into database yesterday, and today I’m going to share how to use MySQL group by to organise your database.
Through the image above you could see that the data is divided with different district to each day. Let’s assume that the data of the rightest column is rainfall data, what if we want to access the average rainfall of each day from all districts?
We could use MySQL-group by to achieve it.
Take a look on the snippet of code below:
select date, avg(rainfall) rainfall from rainfall group by date;
The first date in the above code means name of the column, avg means to average the amount inside the braces, which means to average the amount of value on the rainfall column.
The second rainfall is the name of this table, and the final date means that we reorganise the data with date as its unit. It would enable automatic calculation of averaging on the data on the same date.
The result is as follows:
Here you go! I hope my sharing has been helpful.