Aggregate Avg > 0

Is it possible to calculate an aggregate average specifically for all values
> 0?Mike
Done on Northwind Database
select avg(unitprice),orderid
from [order details]
group by orderid
having avg(unitprice)>20--change it
"Mike" <mike@.hello.com> wrote in message
news:%23QqEX2MnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Is it possible to calculate an aggregate average specifically for all
> values
>|||> "Mike" <mike@.hello.com> wrote in message
> news:%23QqEX2MnFHA.3448@.TK2MSFTNGP12.phx.gbl...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OQmOw%23MnFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Mike
> Done on Northwind Database
> select avg(unitprice),orderid
> from [order details]
> group by orderid
> having avg(unitprice)>20--change it
Sorry, I didn't clarify exactly what I was looking for.
Say we have Table1:
ID |Count | Average
===========
1 | 1 | 7.2
2 | 2 | 6.8
3 | 2 | 0
4 | 5 | 8.4
5 | 2 | 0
I want to produce the following aggregate totals (all in the one query):
CountSum = 1+2+2+5+2 (This is obviously very easy)
and
AverageAvg = (7.2+6.8+8.4)/3
i.e. Note that the average function only averages results greater than zero.
That's what I'm looking for.|||Mike
create table #test
(
[id]int not null primary key,
[count] int not null,
[Average] decimal(5,2) not null
)
go
insert into #test values (1,1,7.2)
insert into #test values (2,2,6.2)
insert into #test values (3,2,0)
insert into #test values (4,5,8.4)
insert into #test values (5,2,0)
select avg(average) from #test
where average>0
"Mike" <mike@.hello.com> wrote in message
news:%23P%23F$FNnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OQmOw%23MnFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Sorry, I didn't clarify exactly what I was looking for.
> Say we have Table1:
> ID |Count | Average
> ===========
> 1 | 1 | 7.2
> 2 | 2 | 6.8
> 3 | 2 | 0
> 4 | 5 | 8.4
> 5 | 2 | 0
> I want to produce the following aggregate totals (all in the one query):
> CountSum = 1+2+2+5+2 (This is obviously very easy)
> and
> AverageAvg = (7.2+6.8+8.4)/3
> i.e. Note that the average function only averages results greater than
> zero. That's what I'm looking for.
>|||Select AVG(<Yourcolumn> ) from sometable where <Yourcolumn> > 0 ?
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OQmOw%23MnFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Sorry, I didn't clarify exactly what I was looking for.
> Say we have Table1:
> ID |Count | Average
> ===========
> 1 | 1 | 7.2
> 2 | 2 | 6.8
> 3 | 2 | 0
> 4 | 5 | 8.4
> 5 | 2 | 0
> I want to produce the following aggregate totals (all in the one query):
> CountSum = 1+2+2+5+2 (This is obviously very easy)
> and
> AverageAvg = (7.2+6.8+8.4)/3
> i.e. Note that the average function only averages results greater than zer
o.
> That's what I'm looking for.
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ethdQNNnFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Mike
> create table #test
> (
> [id]int not null primary key,
> [count] int not null,
> [Average] decimal(5,2) not null
> )
> go
> insert into #test values (1,1,7.2)
> insert into #test values (2,2,6.2)
> insert into #test values (3,2,0)
> insert into #test values (4,5,8.4)
> insert into #test values (5,2,0)
>
> select avg(average) from #test
> where average>0
Yes, that is a way of calculating the average, but it isn't in the same
query as the SUM aggregate function. I want to find a quick way of doing the
AVG > 0 function alongside the SUM function. If this isn't possible then so
be it.|||"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.
de>
wrote in message news:29C15C0D-7617-403B-9203-7CF535BD4799@.microsoft.com...
> Select AVG(<Yourcolumn> ) from sometable where <Yourcolumn> > 0 ?
> --
> HTH, Jens Suessmeyer.
Yes, that is the easy but long-winded version. I want to achieve this in
once single query alongside the SUM function.
e.g.
SELECT SUM(NBSTWPremSum) AS NBSTWPremSumTotal, AVG(SELECT NBSTWPremAvg WHERE
NBSTWPremAvg >0) AS NBSTWPremAvgTotal FROM tblTempOpStats
The above is illegal but hopefully gives an indicator of what I'm trying to
achieve.|||Mike
Have you read your own posts? Did
mention about SUM function NOTHING.
"Mike" <mike@.hello.com> wrote in message
news:u7iQORNnFHA.1968@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ethdQNNnFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Yes, that is a way of calculating the average, but it isn't in the same
> query as the SUM aggregate function. I want to find a quick way of doing
> the AVG > 0 function alongside the SUM function. If this isn't possible
> then so be it.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:egBdAcNnFHA.860@.TK2MSFTNGP12.phx.gbl...
> Mike
> Have you read your own posts? Did
> mention about SUM function NOTHING.
I said:
"I want to produce the following aggregate totals (all in the one query):
CountSum = 1+2+2+5+2 (This is obviously very easy)
and
AverageAvg = (7.2+6.8+8.4)/3"|||I think this is what you are after:
create table #test
(
[id] int not null primary key,
[count] int not null,
[average] decimal(5,2) not null
)
go
insert into #test values (1,1,7.2)
insert into #test values (2,2,6.2)
insert into #test values (3,2,0)
insert into #test values (4,5,8.4)
insert into #test values (5,2,0)
select sum(count), avg(case when average > 0 then average else null end)
from #test
drop table #test
R
"Mike" <mike@.hello.com> wrote in message
news:%23uk$QrNnFHA.2916@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:egBdAcNnFHA.860@.TK2MSFTNGP12.phx.gbl...
> I said:
> "I want to produce the following aggregate totals (all in the one query):
> CountSum = 1+2+2+5+2 (This is obviously very easy)
> and
> AverageAvg = (7.2+6.8+8.4)/3"
>