?
Solved

Average daily volumes

Posted on 2005-04-12
11
Medium Priority
?
636 Views
Last Modified: 2008-03-03
I have a table containing trades from various endpoints.  How do I get the average daily volume for a given endpoint?
I'm doing this, but I'm pretty sure this is not an avg daily volume.

select destination as EndPoint, DATEPART(dd, timeofexecution) as Daily, AVG(lastfillquantity)
from trading.dbo.trade
WHERE destination IN ('BBBB','III')
GROUP BY destination, datepart(dd,timeofexecution)
order by datepart(dd,timeofexecution) desc
0
Comment
Question by:RLLewis
  • 6
  • 5
11 Comments
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13768953
Hi RLLewis!

Perhaps, this is what you are looking for :

Select destination as EndPoint, CONVERT(varchar,timeofexecution,101) as Daily, AVG(lastfillquantity)
From trading.dbo.trade
Where destination IN ('BBBB','III')
Group by destination, CONVERT(varchar,timeofexecution,101)
Order by CONVERT(varchar,timeofexecution,101) desc

*Note : I assume that the datatype of "timeofexecution" is datetime.

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13768991
it looks good, entrance, but it's pretty much what i've been getting, except for better on the dates.  possibly i've misunderstood the request.
let me ask you --- average daily volume is avg of the quantity traded, yes?  i wouldn't need to calc the value of those trades and then avg that, would I ?   see here, i'm getting the total count of trades placed, the total quantity of the trades, and then the total $ figure of said trades.  to do an avg, it's just avg(lastfillquantity), right?
 am i missing something?

SELECT 'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(count(*) AS MONEY),1),13)),+''+
'Stocks Volume -   '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(LastFillQuantity) AS MONEY),1),13)),+''+
'Total $  - '+RIGHT('     ' + ISNULL(CONVERT(varchar(20),SUM(LastFillQuantity*CAST(LastFillPrice AS Money)),1),0.00),20)
FROM trading.dbo.Trade WITH (NOLOCK)
WHERE timeofexecution >= cast(convert(varchar(7), getdate(), 102)+'.01' AS DATETIME)
AND destination IN ('BBBB','IIII')

0
 
LVL 1

Author Comment

by:RLLewis
ID: 13769033
Possibly an average of the sum(lastfillquantity) ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Renante Entera
ID: 13769045
Can you post sample data ???

If possible, post also the result that you want to accomplish...
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13769065
i can't really post anything.  but here's the sum(lastfillquantity) for bbbb for today:  5003202
and here it is for yesterday:  4350322
....and so on

how would i get the avg of that daily sum for the last month or so?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13769074
i attempt to do this:   avg(sum(lastfillquantity))

but i error

Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13769095
Hmmm ... :-?  Let me think first ...  Actually, it would be better if you can provide me sample data :-)

> i attempt to do this:   avg(sum(lastfillquantity))
   Of course, you will really expect an error 'coz you need to have a "group by" when you have an aggregate function.  Can you post your final query ???

Regards!
eNTRANCE2002 :-)
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13769107
i've got the group by

Select destination as EndPoint, CONVERT(varchar,timeofexecution,101) as Daily, AVG(SUM(lastfillquantity))
From trading.dbo.trade
Where destination IN ('BBBB','IIII')
Group by destination, CONVERT(varchar,timeofexecution,101)
Order by destination, CONVERT(varchar,timeofexecution,101) desc
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 375 total points
ID: 13769301
Ah!  I got now that you want to achieve.  In this case, you don't need to use AVG() function.

Try this :

Select destination as EndPoint, CONVERT(varchar,timeofexecution,101) as Daily, (SUM(cast(lastfillquantity as money))/COUNT(*)) as AvgVolume
From trading.dbo.trade
Where destination IN ('BBBB','IIII')
Group by destination, CONVERT(varchar,timeofexecution,101)
Order by destination, CONVERT(varchar,timeofexecution,101) desc

*Note : If you will consider decimal places, assuming until two(2) decimal places then you need to cast the result as numeric(12,2).

This will be the query :

Select destination as EndPoint, CONVERT(varchar,timeofexecution,101) as Daily, cast((SUM(cast(lastfillquantity as money))/COUNT(*)) as numeric(12,2)) as AvgVolume
From trading.dbo.trade
Where destination IN ('BBBB','IIII')
Group by destination, CONVERT(varchar,timeofexecution,101)
Order by destination, CONVERT(varchar,timeofexecution,101) desc

Let me know the result.


Regards!
eNTRANCE2002 :-)
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13773823
well, i believe this is correct.  I am being told, however, that the 'numbers don't look right'.  thank you for your help, entrance.  i'm going to keep going through it.
oh, also, if i wanted to get the trade volume (lastfillquantity * lastfillprice), how would you suggest i get avg and sum counts per endpoint?
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13778103
Hi RLLewis!

What would be the format of the numbers that you want to accomplish ???  I have provided two(2) queries depending on a situation.  Have you tried running both queries ?

> oh, also, if i wanted to get the trade volume (lastfillquantity * lastfillprice), how would you suggest i get avg and sum counts per endpoint?
   I think this should be a different question :-?


Regards!
eNTRANCE2002 :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question