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
> > 0?
>|||> "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...
>> 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
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...
>> "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...
>> 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
> 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:
> > "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...
> >> 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
> 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.
>
>|||"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 Süßmeyer" <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...
>> 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.
>|||"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...
> > 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"
>|||You can use a non-correlated subquery to return the desired average
independently of the SUM. For example:
SELECT
SUM(NBSTWPremSum) AS NBSTWPremSumTotal,
(SELECT
AVG(NBSTWPremAvg)
FROM tblTempOpStats
WHERE NBSTWPremAvg > 0) AS NBSTWPremAvgTotal
FROM NBSTWPremSumTotal
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mike@.hello.com> wrote in message
news:eZL1yVNnFHA.3936@.TK2MSFTNGP10.phx.gbl...
> "Jens Süßmeyer" <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.
>|||On Tue, 9 Aug 2005 12:00:31 +0100, Mike wrote:
(snip)
>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
Hi Mike,
SELECT SUM(Count),
AVG(CASE WHEN Average > 0 THEN Average END)
FROM MyTable
If the Average column can never be negative (i.e. it is 0 -and has to be
excluded- or >0), then you can use this shorter version:
SELECT SUM(Count),
AVG(NULLIF(Average, 0))
FROM MyTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||OR ...
What about ...
SELECT SUM(Count),
AVG(Average)
FROM MyTable
WHERE Average <> 0
That seems easier to me, but I haven't seen the entire post, so I am not
sure.
HTH.
Daren Bieniek, MCDBA, MCSE
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ou4if1p8d2gpjasotrq9vpmecu49judjh2@.4ax.com...
> On Tue, 9 Aug 2005 12:00:31 +0100, Mike wrote:
> (snip)
> >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
> Hi Mike,
> SELECT SUM(Count),
> AVG(CASE WHEN Average > 0 THEN Average END)
> FROM MyTable
> If the Average column can never be negative (i.e. it is 0 -and has to be
> excluded- or >0), then you can use this shorter version:
> SELECT SUM(Count),
> AVG(NULLIF(Average, 0))
> FROM MyTable
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)