trunk/sql/backups_full.sql

-- report number of files and staticics for each backup

select
        hosts.name as host,
        shares.name as share,
        backupnum as num,
        backups.date as backup_date,
        backups.type as type,
        min(files.date) as from,
        max(files.date) as to,
        count(*) as files,
        sum(files.size) as size,
        sum(backups.size) as backup_size
from files
join shares on files.shareid=shares.id
join hosts on shares.hostid = hosts.id
join backups on shares.hostid = backups.hostid
        and files.backupnum = backups.num
        and shares.id = backups.shareid
group by shares.name, hosts.name, backupnum, backups.date, backups.type
order by backups.date;
1	-- report number of files and staticics for each backup
2
3	select
4	hosts.name as host,
5	shares.name as share,
6	backupnum as num,
7	backups.date as backup_date,
8	backups.type as type,
9	min(files.date) as from,
10	max(files.date) as to,
11	count(*) as files,
12	sum(files.size) as size,
13	sum(backups.size) as backup_size
14	from files
15	join shares on files.shareid=shares.id
16	join hosts on shares.hostid = hosts.id
17	join backups on shares.hostid = backups.hostid
18	and files.backupnum = backups.num
19	and shares.id = backups.shareid
20	group by shares.name, hosts.name, backupnum, backups.date, backups.type
21	order by backups.date;