Re: Help with select statement

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
>
>