vishal veerkar wrote: > Hi All, > > I would like to calculate the last 7days total backup utilization of my > clients. I presently using 2.2.8 with Mysql in catalog DB. I was trying > to tweak one query from query.sql as below. Frankly speaking i am not a > DB expert and just want to get the job done. > > :List all backups for a Client after a specified time and size > *Enter Client Name: > *Enter time in YYYY-MM-DD HH:MM:SS format: > SELECT DISTINCT Job.JobId,Client.Name as > Client,Level,StartTime,JobFiles,*sum(JobBytes)* as Totalsize,VolumeName > FROM Client,Job,JobMedia,Media > WHERE Client.Name='%1' > AND Client.ClientId=Job.ClientId > AND JobStatus='T' > AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId > AND Job.StartTime >= '%2' > * GROUP BY Client.Name;* > > Actually it gives the out put but the size does not match the actual size.
MySQL silently does unexpected things. PostgreSQL does this: SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,sum(JobBytes) as Totalsize,VolumeName FROM Client,Job,JobMedia,Media WHERE Client.Name='polo-fd' AND Client.ClientId=Job.ClientId AND JobStatus='T' AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND Job.StartTime >= '2009-12-01' GROUP BY Client.Name; ERROR: column "job.jobid" must appear in the GROUP BY clause or be used in an aggregate function It expected standard SQL. So we change to Standard SQL by removing the columns we are not grouping on. SELECT Client.Name as Client, sum(JobBytes) as Totalsize FROM Client,Job,JobMedia,Media WHERE Client.Name='polo-fd' AND Client.ClientId=Job.ClientId AND JobStatus='T' AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND Job.StartTime >= '2009-12-01' GROUP BY Client.Name; client | totalsize ---------+------------- polo-fd | 11971658478 (1 row) ------------------------------------------------------------------------------ This SF.Net email is sponsored by the Verizon Developer Community Take advantage of Verizon's best-in-class app development support A streamlined, 14 day to market process makes app distribution fast and easy Join now and get one step closer to millions of Verizon customers http://p.sf.net/sfu/verizon-dev2dev _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
