0

Quite new to mysql and have trouble formulating this type of query.

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

….. and so forth

I know I can just do simple select sum(numberofUser) from tableX where server like ‘server1’ order by timestamp desc limit 100; to get total of users for server1 for last 100 timestamp

But how would you do this for all 3 servers? I want to aggregate results for all 3 servers for 100 timestamp for each of the servers.. just quite not sure how to do this. Please help.

Shadow
  • 33,525
  • 10
  • 51
  • 64
user3502374
  • 781
  • 1
  • 4
  • 12
  • Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Shadow Jun 07 '16 at 16:19
  • You cant run sql across servers :(, you have to use some programatic intermediary – Rhys Bradbury Jun 07 '16 at 16:19
  • I will check out that post.. And yes, this is only from one sql server(under one table) – user3502374 Jun 07 '16 at 16:20

1 Answers1

1

Use group by 

select sum(numberofUser) , server
from tableX 
group by server

and limit your time period 

select sum(numberofUser) , server
from tableX 
where timestamp between 'your_date_min' AND 'your_date_max'
group by server

The total for all the servers is simply 

select sum(numberofUser) 
from tableX

for the last 10 row of server1 and server2

the totale for single server 

select sum(numberofUser), server 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
group by server

or total for the 2 server 

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
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • how would you add them up? instead of by server, how would sum up the total from all server? – user3502374 Jun 07 '16 at 17:55
  • Anyway i have update the answer .. hope is this what you are looking for .. and that may answer is useful for you. – ScaisEdge Jun 07 '16 at 18:05
  • so say, I wanted to I want to get total for server 1 and server 2 together for last 10 samples of each server.. that's what I wanted to run.. so something like.. select sum(numberofUser) where server like 'server1' or 'server2' order by desc 10 – user3502374 Jun 07 '16 at 18:22
  • so my question is, if I did below, is this truly taking 10 samples from each server or 10 samples for whatever it matches select sum(numberofUser) from tableX where server like 'server1' or 'server2' order by timestamp limit 10; – user3502374 Jun 07 '16 at 18:26
  • another words, based on timestamp, take last 10 samples from each server(say server1 and server2) and sum it – user3502374 Jun 07 '16 at 18:29
  • getting "Incorrect usage of UNION and order by" but I think you are on the path that I want to go – user3502374 Jun 07 '16 at 18:43
  • Solved ...5 minutes for the solution – ScaisEdge Jun 07 '16 at 19:41
  • thank you so much.. I assume I can also do order by date desc limit 10 right? I have accepted your answer btw, much appreciate it!! – user3502374 Jun 07 '16 at 20:17
  • actually, one more question, how would you output for each date? so.. if I want to output for each of the timestamp for total (server1+2)? Meaning, as long as there is unique timestamp(which there should be 10 of them), I want to total by timestamp.(well, 10 for each server but then add them according to right timestamp) – user3502374 Jun 07 '16 at 21:24