SQL tool for MSDE: avg

Showing posts with label avg. Show all posts

Thursday, March 8, 2012

Aggregate Problem

When I run the my code in Northwind, the ExtendedPrice is wrong because
UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice in
my statement because I couldn't figure out a better aggregate function to
use.
My goal is to simply get a GROUP by Orders.OrderDate and SUM the
ExtendedPrice expression for each date. I probably need some type of
subquery, but not sure how to go about this one.
CODE (used in northwind database):
SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
[Order Details].UnitPrice * [Order Details].Quantity)) AS ExtendedPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderDateHi
It may be clearer is you give your expected results, but this may be a
starting point!
SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
FROM Orders O
JOIN [Order Details] D ON O.OrderID = D.OrderID
GROUP BY O.OrderDate
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
> When I run the my code in Northwind, the ExtendedPrice is wrong because
> UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice
> in my statement because I couldn't figure out a better aggregate function
> to use.
> My goal is to simply get a GROUP by Orders.OrderDate and SUM the
> ExtendedPrice expression for each date. I probably need some type of
> subquery, but not sure how to go about this one.
> CODE (used in northwind database):
> SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
> COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
> [Order Details].UnitPrice * [Order Details].Quantity)) AS
> ExtendedPrice
> FROM Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order Details].OrderID
> GROUP BY Orders.OrderDate
>|||that did it. thx.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uty7J2Y3FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi
> It may be clearer is you give your expected results, but this may be a
> starting point!
> SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
> FROM Orders O
> JOIN [Order Details] D ON O.OrderID = D.OrderID
> GROUP BY O.OrderDate
> John
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
>

Aggregate Functions Support?

Does anyone know if the jdbc driver for SQL Server
support aggregate functions such as AVG, SUM, COUNT ?
I can't find any documentation stating it does or
doesn't, but my testing seems to show that it does not.
Thanks
JD
JD wrote:

> Does anyone know if the jdbc driver for SQL Server
> support aggregate functions such as AVG, SUM, COUNT ?
> I can't find any documentation stating it does or
> doesn't, but my testing seems to show that it does not.
> Thanks
> JD
In general, the driver doesn't care about what SQL you
send to the DBMS. What problems are you having using
those functions?
Joe Weinstein at BEA
|||The result set is empty. I can successfully run the query
manually via isql.
On second thought perhaps it's an issue with the version
of java I'm running, JDK 1.2.2, that might not support
the aggregate functions.
JD
>--Original Message--
>
>JD wrote:
>
>In general, the driver doesn't care about what SQL you
>send to the DBMS. What problems are you having using
>those functions?
>Joe Weinstein at BEA
>.
>
|||anonymous@.discussions.microsoft.com wrote:

> The result set is empty. I can successfully run the query
> manually via isql.
> On second thought perhaps it's an issue with the version
> of java I'm running, JDK 1.2.2, that might not support
> the aggregate functions.
Hi. No that's not it either. If the driver doesn't care about your SQL,
the JVM certainly won't. SHow your JDBC code and function definition.
Then show what isql shows when you call it.
Joe Weinstein at BEA
[vbcol=seagreen]
> JD
|||<anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2078b01c45933$f788d340$a401280a@.phx.gbl...
> The result set is empty. I can successfully run the query
> manually via isql.
> On second thought perhaps it's an issue with the version
> of java I'm running, JDK 1.2.2, that might not support
> the aggregate functions.
Nope. I'd guess that it's rather a database connection thing: do you
connect to the same database via JDBC and ISQL? Do you use the same
credentials?
robert
[vbcol=seagreen]
> JD

Tuesday, March 6, 2012

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)

Thursday, March 8, 2012

Aggregate Problem

Aggregate Functions Support?

Tuesday, March 6, 2012

Aggregate Avg > 0

Aggregate Avg > 0

Aggregate Avg > 0

SQL tool for MSDE

Blog Archive

About Me