0
So, I have a table that records the login of the players, all values in Unix timestamp, and I wanted to know how to know the day that there were more records, and, adding the total, the day that there were more Seg_online, Aj_sent and Lvl_upado.
Using FROM_UNIXTIME function:
0
To group by date and add the columns seg_online, aj_sent 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;
Upshot:
+------------+------------+-------------+-----------+
| 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, select the date and 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 row 1 to column aux.lvl_upado by the desired column.
I got it, thank you
Thanks for the feedback, for nothing!
Browser other questions tagged mysql sql timestamp unix
You are not signed in. Login or sign up in order to post.
I believe that can be used the
GROUP BY. Another suggestion, in the questions it is interesting to post the code text with the table structure (CREATE TABLE) and other code snippets, it is easier for those who help you with the problem.– Camilo Santos
Pedro if possible post the structure and query in SQL Fiddle, to help gets better
– Clayton Tosatti