Solved

# Average daily volumes

Posted on 2005-04-12
Medium Priority
636 Views
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)
WHERE destination IN ('BBBB','III')
GROUP BY destination, datepart(dd,timeofexecution)
order by datepart(dd,timeofexecution) desc
0
Question by:RLLewis
• 6
• 5

LVL 14

Expert Comment

ID: 13768953
Hi RLLewis!

Perhaps, this is what you are looking for :

Select destination as EndPoint, CONVERT(varchar,timeofexecution,101) as Daily, AVG(lastfillquantity)
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

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)
WHERE timeofexecution >= cast(convert(varchar(7), getdate(), 102)+'.01' AS DATETIME)
AND destination IN ('BBBB','IIII')

0

LVL 1

Author Comment

ID: 13769033
Possibly an average of the sum(lastfillquantity) ?
0

LVL 14

Expert Comment

ID: 13769045
Can you post sample data ???

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

LVL 1

Author Comment

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

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

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

ID: 13769107
i've got the group by

Select destination as EndPoint, CONVERT(varchar,timeofexecution,101) as Daily, AVG(SUM(lastfillquantity))
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

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
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
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

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

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

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ā¦