user3502374 user3502374 - 5 months ago 15
MySQL Question

mysql how to extract data from multiple and sum

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.

Answer

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
Comments