Aggregate functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Aggregate functions

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-08-16 : 07:56:10

Hi All

can anyone help with the following ?

I have the following table



date                                quantity
2002-07-15 14:24:38.000              50000
2002-07-25 12:46:08.000              50000
2002-07-25 09:42:08.000              15500
2002-07-30 16:16:18.000              50000
2002-08-01 10:44:21.000              10000
2002-08-07 09:15:26.000              50000

what I want is to calculate the total daily average

i.e 225,500 / no of days(5) this should = 45,100

I am using the following queuery but this is not right it produces a result of 37583.333333.



select avg(quantity) from 
(Select Distinct  cast(Month(DEaldate) as varchar(2))+'/'+ cast(Day(dealdate) as varchar(2))+'/'+cast(Year(dealdate)as  varchar(4)) dealdate 
FROM  TradeData..tradehistory (NOLOCK)) a
inner join TradeData..tradehistory t
on a.dealdate = cast(Month(t.dealdate) as varchar(2))+'/'+ cast(Day(t.dealdate) as varchar(2))+'/'+cast(Year(t.dealdate) as varchar(4)) 
WHERE t.Dealdate between '14 jul 2002 12:00AM' and  '14 aug 2002 12:00AM'
and symbol = 'rec' 
and cancelled <> 1

It basically is calculating the average as 225,500 / no of records.

I hope this makes sense, can any one help me to get the desired result?

Thanks in Advance
OMB

Edited by - omb on 08/16/2002 07:56:50

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-16 : 08:21:49

How do you expect anyone to debug your select statement when you don't provide a table definition/create table statement (DDL) AND the sample data you give doesn't match the structure implied by your DML?

Whats dealdate? whats symbol? whats cancelled?

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-08-16 : 08:26:02

Jay is right, but based on the table containing only the fields date and quantity, here's a way to do it:

select avg(quantity) as [Average Quantity]
from
(select convert(varchar, date, 112) as date, sum(quantity) as quantity
from TradeData..tradehistory (nolock)
group by convert(varchar, date, 112)) as quantsum

You basically calculate the sum per day in a query, then run the average function on that.

HTH,
Kalle Dahlberg

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-08-16 : 09:38:45

sorry guys

In a rush to get this on the forum i missed out some information accidently

here is the revised table structure



Dealdate                             quantity    cancelled    symbol
2002-07-15 14:24:38.000              50000           0          REC
2002-07-25 12:46:08.000              50000           0          REC
2002-07-25 09:42:08.000              15500           0          REC
2002-07-30 16:16:18.000              50000           0          REC
2002-08-01 10:44:21.000              10000           0          REC
2002-08-07 09:15:26.000              50000           0          REC

i hope this clears things up sorry about the initial dodgy thread.

OMB

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-16 : 09:40:54


SELECT SUM(quantity) / COUNT(DISTINCT DATEADD(day, DATEDIFF(day, 0, dealdate), 0))
FROM TradeData..tradehistory (NOLOCK)
WHERE Dealdate >= '2002-07-14' AND Dealdate < '2002-08-14'
  AND symbol = 'rec' 
  AND cancelled <> 1

Is an alternative. (Yes I changed the BETWEEN because I didn't believe in it!)

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-08-16 : 09:48:23

Arnold, thanks for that I did do it this way initially, but i also had to work out the standard deviation and did not fancy working that one out manually.

Aandraax thanks for your solution it works fine and also for my standard deviation calculation

once again thanks for all those who replied

OMB

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-16 : 09:53:54

Kalle's (Andraax) is probably faster anyway (if you change the date CONVERT to DATEADD/DATEDIFF

). Having both SUM and COUNT(DISTINCT) means my query will generate a plan with the two aggregations in 'parallel' and then join their results, rather than do the second aggregation on the result of the first.

Edited by - Arnold Fribble on 08/16/2002 09:55:57

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources