Output Column for Complex Query

Question

I would like to ask as to how to output a column with the tickerID.

As of now I have the following:

select distinct 
    mtime,  
    avg(lastBid) Bid ,
    avg(lastAsk) Ask 
from 
    (Select 
         a.mtime, a.IntradayTime, f.lastBid, f.lastAsk 
     from 
         (select 
              cte.*, 
              (select top 1 datetime 
               from IntradayHistory_1min.dbo.IntradayDataHistory' + @product + '_1min' + ' 
               where datetime <= cte.mtime 
                 and TickerID = ' + cast(@ticker as nvarchar(24)) + ' 
               order by datetime desc) as IntradayTime 
          from 
              cte) a 
    left join 
        Intradayhistory_1min.dbo.IntradayDataHistory'+ @product + '_1min' + ' f on f.datetime = a.IntradayTime and f.tickerid = ' + cast(@ticker as nvarchar(24)) + ') b 
group by 
    mtime 
order by 
    mtime 
option (maxrecursion 0)

A screenshot of dbo.IntradayDataHistory_Rebar_1min (in this case: Rebar is @product):

Updated for clarification: Output to look like this,

I have tried the following too, which didn't work:

select distinct mtime,
(ticker) tickerid
avg(lastBid) Bid ,
avg(lastAsk) Ask 

from ( Select a.mtime, a.IntradayTime, f.tickerid, f.lastBid, f.lastAsk 
from (select cte.*, (select top 1 datetime from IntradayHistory_1min.dbo.IntradayDataHistory' + @product + '_1min' + ' 
where datetime <= cte.mtime and TickerID = ' + cast(@ticker as nvarchar(24)) + ' order by datetime desc) as IntradayTime from cte ) a 
left join Intradayhistory_1min.dbo.IntradayDataHistory'+ @product + '_1min' + ' f 

on f.datetime = a.IntradayTime and f.tickerid = ' + cast(@ticker as nvarchar(24)) + ' ) b 
group by mtime order by mtime option (maxrecursion 0)

The error I get:

Column 'b.tickerid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Unclear what you're trying to do - but we can see that, logically, the `tickerid` must also be whatever is contained in the `@RBticker1` variable, the simplest approach may be to select that. — Damien_The_Unbeliever, Jul 25 '17 at 07:01
And if you add `tickerid` to GROUP BY clause? `group by mtime, tickerid order by ... ` — Vojtěch Dohnal, Jul 25 '17 at 07:37

score 0 · Accepted Answer · answered Jul 25 '17 at 08:23

I found out the solution is to actually add an avg function in front of ticker, so as followed:

select distinct mtime,
avg(ticker) tickerid
avg(lastBid) Bid ,
avg(lastAsk) Ask 

from ( Select a.mtime, a.IntradayTime, f.tickerid, f.lastBid, f.lastAsk 
from (select cte.*, (select top 1 datetime from IntradayHistory_1min.dbo.IntradayDataHistory' + @product + '_1min' + ' 
where datetime <= cte.mtime and TickerID = ' + cast(@ticker as nvarchar(24)) + ' order by datetime desc) as IntradayTime from cte ) a 
left join Intradayhistory_1min.dbo.IntradayDataHistory'+ @product + '_1min' + ' f 

on f.datetime = a.IntradayTime and f.tickerid = ' + cast(@ticker as nvarchar(24)) + ' ) b 
group by mtime order by mtime option (maxrecursion 0)

The reason behind is in here: Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

you didn't specify in your question that you need an average in your ticker, may be you look for something else? what is the point of taking average of an ID? — Hatik, Jul 27 '17 at 03:54

Output Column for Complex Query

1 Answers1