So, I have a table that registers the login of the players, all the values in unix timestamp, and I wanted to know how I can know the day when there were more records, and adding the total, the day that there were more SEG_Online , AJ_Envadas and LVL_Upado.
UsingfunctionFROM_UNIXTIME:
To group by date and add columns seg_online, aj_envadas and lvl_upado, just do the following:
select from_unixtime(ts_sessao, '%d/%m/%Y') data, sum(seg_online) seg_online,
sum(aj_enviadas) aj_enviadas , sum(lvl_upado) lvl_upado
from logins
group by data;
Result:
+------------+------------+-------------+-----------+
| data | seg_online | aj_enviadas | lvl_upado |
+------------+------------+-------------+-----------+
| 07/10/2018 | 105 | 7 | 9 |
| 08/10/2018 | 220 | 3 | 4 |
+------------+------------+-------------+-----------+
Now, to get the maximum value of each column, make a select looking for the date and the value of the desired column.
select aux.data, max(aux.lvl_upado) quantidade_lvl_upado from (
select from_unixtime(ts_sessao, '%d/%m/%Y') data, sum(seg_online) seg_online,
sum(aj_enviadas) aj_enviadas , sum(lvl_upado) lvl_upado
from logins
group by data) aux;
Return:
+------------+----------------------+
| data | quantidade_lvl_upado |
+------------+----------------------+
| 07/10/2018 | 9 |
+------------+----------------------+
To know the other columns, just change the column aux.lvl_upado in line 1 to the column you want.