Average daily volumes

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
LVL 1
RLLewisAsked:
Who is Participating?
 
Renante EnteraConnect With a Mentor Senior PHP DeveloperCommented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
RLLewisAuthor Commented:
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
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.

 
RLLewisAuthor Commented:
Possibly an average of the sum(lastfillquantity) ?
0
 
Renante EnteraSenior PHP DeveloperCommented:
Can you post sample data ???

If possible, post also the result that you want to accomplish...
0
 
RLLewisAuthor Commented:
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
 
RLLewisAuthor Commented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
RLLewisAuthor Commented:
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
 
RLLewisAuthor Commented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.