I am trying to group it by the date portion of start_time similar to this tsm: SERVER1> SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) - FROM summary WHERE end_time>current_timestamp-(7)DAY and ( activity='ARCHIVE' OR - activity='BACKUP' OR activity='RESTORE' OR activity='RETRIEVE' ) GROUP BY entity, activity
ENTITY ACTIVITY Unnamed[3] ------------------ ------------------ ---------- SERVER-01 ARCHIVE 81.14 SERVER-01 BACKUP 261.68 SERVER-01 RESTORE 2.91 SERVER-02 ARCHIVE 171.51 SERVER-02 BACKUP 0.00 SERVER-03 ARCHIVE 17.64 SERVER-04 ARCHIVE 168.32 SERVER-04 BACKUP 530.77 on http://thobias.org/tsm/sql/index.html#toc81 On Tue, Mar 16, 2010 at 1:23 PM, Richard Sims <[email protected]> wrote: > On Mar 16, 2010, at 1:15 PM, yoda woya wrote: > > > I am trying to run this sql and cannot get a result > > > > select date(start_time), sum(bytes) > > > > from > > summary > > where > > ACTIVITY = 'BACKUP' > > or ACTIVITY = 'RESTORE' > > > > group by date(start_time) > > > > any reasons why?? > > Probably, performance. > Using OR is considerably less efficient than using IN; > so try "... where ACTIVITY in ('BACKUP','RESTORE') ..." > > Further, if you never need Summary data older than a certain number of > days, consider having your SUMmaryretention value close to that, to reduce > bulk in the table. > > Richard Sims http://people.bu.edu/rbs/ >
