user3502374 user3502374 - 5 months ago 9
MySQL Question

breaking down ouput per timestamp by group on mysql

New to mysql,

Under one table(table called tableX), let’s say structure is like below

server date numberofUser
server1 timestamp 3
server2 timestamp 2
server3 timestamp 7
server1 timestamp 2
server2 timestamp 8
server3 timestamp 4


I want to collect for last 100 occurrence(so by the timestamp) but for each timestamp(which means for timestampX(which all server1,2,3 are measured), I want to total numberofUser for server1 and server 2.

So desire output is

timestamp1 5 (which comes from server1(2) and server2(3) added
timestamp2 24 (which comes from server1(12) and server2(12) added
timestamp3 7 (which comes from server1(3) and server2(4) added


Can someone please point me to right direction? thank you

I already got total for last x occurrence of all servers(server 1 and 2) as below which works fine but now I need to break down by each timestamp

select sum(numberofUser) from (
(select server, date, numberofUser from tableX
where server = 'server1'
order by date limit 10)
union
( select server, date, numberofUser from tableX
where server = 'server2'
order by date limit 10 )) as t


::::::::::::::::::::::::::::::UPDATE::::::::::::::::::

http://sqlfiddle.com/#!9/7bc7f/1

I have my scheme above

output I want to see is below for given scheme

date1 total
1970-01-01 00:00:03 85
1970-01-01 00:00:04 125

Answer

Well to hit your desired output you would have to do limit 2 not the 100 you mention in your question. The second query uses a derived table on the inside that enforces the last ones first (the limit 2), with the outer query flipping the results to ordered as you showed.

select date1,sum(numOfUsers) as total 
from test1 
where server in ('server1','server2') 
group by date1 
order by date1 desc 
limit 2; 
+---------------------+-------+
| date1               | total |
+---------------------+-------+
| 1970-01-01 00:00:04 |   125 |
| 1970-01-01 00:00:03 |    85 |
+---------------------+-------+

Your final query is

select date1,total 
from 
(   select date1,sum(numOfUsers) as total 
    from test1 
    where server in ('server1','server2') 
    group by date1 
    order by date1 desc 
    limit 2 
) xDerived 
order by date1; 
+---------------------+-------+
| date1               | total |
+---------------------+-------+
| 1970-01-01 00:00:03 |    85 |
| 1970-01-01 00:00:04 |   125 |
+---------------------+-------+

xDerived is merely an alias name. Every derived table requires an alias name even if not used explicitly thereafter. Regardless of how silly it looks.