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 cpuallocation 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?
