0

I am running this query to get all returns for a list of tickers:

select return1 
from STOCK_DATA 
where TDATE between 20130101 and 20131231
and TICKER in (SELECT DISTINCT TICKER FROM STOCK_DATA)

However, it stacks all returns for the tickers into one column, but I would like to have a table with return1 columns per each ticker?

1

Perhaps you need to run this as a GROUP BY aggregation using the GROUP_CONCAT function.

SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER;

CAVEAT #1

In your DB Session, you may need to extend the length of [GROUP_CONCAT} output because its default limit is 1024. Run this before each connection to set limit to 1M:

SET group_concat_max_len = 1048576;

CAVEAT #2

The above query will only give you ticker_returns if the stock symbol was traded with the specified TDATE range. If you want all tickers to show up regardless of whether or not it had returns in the specified TDATE range, do this:

SELECT A.TICKER,IFNULL(ticker_returns,'<No Returns>') ticker_returns
FROM
(SELECT DISTINCT TICKER FROM STOCK_DATA) A
LEFT JOIN
(SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER) B
USING (TICKER);

If you want a count for each TICKER, just count the number of commas in the ticker_returns and add 1 if the ticker_turns is not null. If you want a query with the count for each ticker, do this:

SELECT A.TICKER,IFNULL(ticker_return_count,0) ticker_return_count
FROM
(SELECT DISTINCT TICKER FROM STOCK_DATA) A
LEFT JOIN
(SELECT TICKER,COUNT(1) ticker_return_count
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER) B
USING (TICKER);

GIVE IT A TRY !!!

For increased query performance, please create this index during your off hours:

ALTER TABLE STOCK_DATA ADD INDEX (TDATE,TICKER,RETURN1);

This should speed up the query.

2
  • Thanks, I just tried it and the result is indeed a list of returns per ticker, but the tickers are row-wise: AAPL 0.0136061,-0.00274497,-0.00169971,0.00189928,0.000413019,-0.000752663,-0.0000350214,-0.000723465,0.00119615,0.00185357,0.00111747,0.00128181,0.00100163,0.000261131,-0.00102751,-0.00174395,-0.00134492,0.000559147,-0.00255365
    – emcor
    Nov 16 '14 at 16:59
  • Each ticker has 100.000 values, and I want to store it in an excel table, so I need tickers as column (with returns in rows)?
    – emcor
    Nov 16 '14 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.