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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users