I'll take a stab: select cast(entity as varchar(12)) as "Node Name", \ cast(activity as varchar(10)) as Type, \ sum(cast(affected as decimal(7,0))) as files, \ sum (cast(bytes/1024/1024 as decimal(12,4))) as "Phy_MB" \ from summary where end_time>=timestamp(current_date -1 days, '09:00:00') \ and end_time<=timestamp(current_date, '08:59:59') \ and (activity='BACKUP' or activity='ARCHIVE') \ group by entity, activity \ order by "Node Name"
On Thu, 18 Jul 2002, L'Huillier, Denis wrote: >Hello - > >I wrote the following select statement (with a lot of plagiarism). > >/* --- Query Summary Table ---- */ >select cast(entity as varchar(12)) as "Node Name", \ >cast(activity as varchar(10)) as Type, \ >cast(affected as decimal(7,0)) as files, \ >cast(bytes/1024/1024 as decimal(12,4)) as "Phy_MB" \ >from summary where end_time>=timestamp(current_date -1 days, '09:00:00') \ >and end_time<=timestamp(current_date, '08:59:59') \ >and (activity='BACKUP' or activity='ARCHIVE') \ >order by "Node Name" > >The problem is, if a node performed 10 backups and 5 archives over the 24 hour period >there are 15 lines for that node in the output, 10 for backup and 5 for archive. >Is there a way I can add the affected columns and bytes column for a node which has >activity=BACKUP and again for those with activity=ARCHIVE ? >Basically, what I want is at most 2 lines per node... 1 line can be the sum of >affected files and bytes for all backup activities >And the other line for that node can be the sum of affected files and bytes for all >ARCHIVE activities. > >I think I'm over my head. > > >Regards, > >Denis L. L'Huillier >212-647-2168 > >