Average problem

[NavMen]

Hi,

Can some help me with the following problem I’m trying to create a query which gives me the Average for a day?

I created the next query, but it’s not reporting one Average value.

----------------------------
SELECT drive, round((cast((avg(sessions)) AS DECIMAL(10,0))),2) as 'conn', rate, round((cast((avg(rate)) AS DECIMAL(10,0))/1024),2) as 'load' From nsrTape

where convert(char(10),GETDATE(),103) = convert(char(10),nsrTape.time_,103)and operation = 'write' and sessions > '0'

Group by drive, rate
---------------------------

Result of this query is:

Drive conn rate load
\\\\.\\Tape0 1 576 .560000
\\\\.\\Tape0 1 1270 1.240000
\\\\.\\Tape0 1 1571 1.530000
\\\\.\\Tape0 1 2551 2.490000
\\\\.\\Tape0 1 2998 2.930000

I like to get the following result:

Drive conn rate load
\\\\.\\Tape0 1 1793.2 1.750000

All help is welcome

/Navmen

 

[SQLSister]

Your problem is the group by clause. You must tell it to calculate the average rate or the min(rate) or max(rate) and take rate out of the group by. Otherwise it must give you the data for each specific rate.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

This should do it. BTW, when you cast to Decimal (10,2) the value will be rounded to 2 places, so you don't need to round again.

[Blue]SELECT[/Blue] drive[Gray],[/Gray] conn[Gray],[/Gray]
       [Fuchsia]Cast[/Fuchsia][Gray]([/Gray][Fuchsia]AVG[/Fuchsia][Gray]([/Gray]sessions[Gray])[/Gray] [Blue]AS[/Blue] [Blue]decimal[/Blue][Gray]([/Gray]10[Gray],[/Gray]2[Gray])[/Gray][Gray])[/Gray] [Blue]AS[/Blue] [red]'rate'[/red][Gray],[/Gray] 
       [Fuchsia]Cast[/Fuchsia][Gray]([/Gray][Fuchsia]AVG[/Fuchsia][Gray]([/Gray]rate[Gray])[/Gray] [Blue]AS[/Blue] [Blue]decimal[/Blue][Gray]([/Gray]10[Gray],[/Gray]2[Gray])[/Gray][Gray])[/Gray]/1024 [Blue]AS[/Blue] [red]'load'[/red]
   [Blue]FROM[/Blue] nsrTape
   [Blue]WHERE[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray] [Gray]=[/Gray] 
         [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]0[Gray],[/Gray]nsrTape.time[Gray])[/Gray][Gray],[/Gray]0[Gray])[/Gray] [Gray]AND[/Gray]
         operation [Gray]=[/Gray] [red]'write'[/red] [Gray]AND[/Gray] sessions [Gray]>[/Gray] [red]'0'[/red]
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] drive[Gray],[/Gray] conn

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

How about DateDiff( dd, getdate(), nsrTape.time = 0 ) only? Will this work?

 

[donutman]

Touché.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]