mysql report sql help

I have mysql table with data like this. record will have server with total cpu and virtual server with cpu assinged

type, cpu
srv1,   10
vsrv11, 2
vsrv12,  3
srv2,   15
vsrv21, 6
vsrv22,  7
vsrv23,  1

from the above data, I want to create output like this.

server, total cpu, assigned cpu, free cpu
srv1, 10, 5, 5
srv2, 15, 14, 1

Can you help me on creating sql query for this report?

I have changed my table and data like this.

CREATE TABLE `cpuallocation` (
  `servertype` varchar(10) DEFAULT NULL,
  `servername` varchar(20) DEFAULT NULL,
  `hostname` varchar(20) DEFAULT NULL,
  `cpu_count` float DEFAULT NULL,
  UNIQUE KEY `server_uniq_idx` (`servertype`,`servername`,`hostname`)

insert into cpuallocation values('srv', 'server1', '',16);
insert into cpuallocation values('vir', 'server1', 'host1',5);
insert into cpuall开发者_运维知识库ocation values('vir', 'server1', 'host2',2.5);
insert into cpuallocation values('vir', 'server1', 'host3',4.5);
insert into cpuallocation values('srv', 'server2', '',8);
insert into cpuallocation values('vir', 'server2', 'host1',5);
insert into cpuallocation values('vir', 'server2', 'host2',2.5);
insert into cpuallocation values('srv', 'server3', '',24);
insert into cpuallocation values('vir', 'server3', 'host1',12);
insert into cpuallocation values('vir', 'server3', 'host2',2);
insert into cpuallocation values('srv', 'server4', '',12);

Update:

I created two view, now I getting the result I want.

create view v1 as 
select servername, sum(cpu_count) as cpu_allocated 
from cpuallocation where servertype='vir' group by servername;

create view v2 as 
select servername, cpu_count as total_cpu 
from cpuallocation where servertype='srv';

select a.servername, a.total_cpu, b.cpu_allocated 
from v2 as a left join v1 as b on a.servername=b.servername;

+------------+-----------+---------------+
| servername | total_cpu | cpu_allocated |
+------------+-----------+---------------+
| server1    |        16 |            12 |
| server2    |         8 |           7.5 |
| server3    |        24 |            14 |
| server4    |        12 |          NULL |
+------------+-----------+---------------+
4 rows in set (0.00 sec)

Is it possible to create a query with-out creating views?

This should be usable for the original table format

select 
  a.type as server,
  a.cpu as total_cpu,
  sum(b.cpu) as assigned_cpu,
  a.cpu-sum(b.cpu) as free_cpu
from cpu as a
left join
  cpu as b on locate(concat('v',a.type), b.type)<>0
where a.type in('srv1', 'srv2')
group by a.type;

Your table does not seems have a common natural key, you should assign one

This should useful for the updated schema:

select
  cpu1.servername as server,
    cpu1.cpu_count as total_cpu,
    ifnull(sum(cpu2.cpu_count), 0) as assigned_cpu,
    ifnull(cpu1.cpu_count-sum(cpu2.cpu_count),0) as free_cpu
from 
  cpuallocation as cpu1
left join
  cpuallocation as cpu2
  on cpu1.servername=cpu2.servername and cpu2.servertype='vir'
where 
  cpu1.hostname = '' 
group by cpu1.servername;

Assuming you can map the virtual servers to the real servers (e.g. through a BELONGS_TO function ), you could join the table on itself or use a subselect as below:

select r.type as server , r.cpu as total, sum(select cpu from tablename v where v.type BELONGS_TO(r.type) ) as assigned, total - assigned as free from tablename r;

For the mapping You could try something like

v.name like CONCAT('%',r.type,'%')

However the results may not be accurate: 'vsrv111' is ambiguous, it could belong to srv11 or srv1.