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	dpavlin	103	-- 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;