breaking down ouput per timestamp by group on mysql

Question

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

also, can someone recommend me a book or tutorial for topic like this? — user3502374, Jun 08 '16 at 15:30
I could help with this, so could anyone, if you created a sqlfiddle, loaded it with real data, and showed a columnar table with formatting showing expected results. As is, this is pretty bad. — Drew, Jun 09 '16 at 02:45
I guess my example is pretty bad? Sorry, but I really don't understand simple problem like this is so difficult to figure out for newbie and was hoping to get some material to comb through. I am looking at some books now and struggle a bit on my own to see if I can figure it out. thank you guys! — user3502374, Jun 09 '16 at 20:09
well load up a fiddle at least and we can help, like this guy did in his [question](http://stackoverflow.com/q/37713790) .... and show what with that fiddle (you only do the left side) what you want your output to look like — Drew, Jun 09 '16 at 20:12
Ok, I updated the original at the bottom w/ fiddle and desired outcome — user3502374, Jun 09 '16 at 20:34
Great! also, if you can recommend any resource, I would love to study further upon on this subject. thank you — user3502374, Jun 09 '16 at 20:43

Drew · Accepted Answer · 2016-06-13T14:14:27.353

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.

I am so sorry, where is xDerived coming from? When I google, I get nothing. — user3502374, Jun 13 '16 at 14:03
I tweaked it slightly. Similar to the derived tables `xParams1` and `xParams2` in the final chunk answer [here](http://stackoverflow.com/a/37791885). Those were two little mini tables used to pump prime some variables used. — Drew, Jun 13 '16 at 14:17

breaking down ouput per timestamp by group on mysql

1 Answers1

Your final query is