MySQL – How to Return Values as Column Table

MySQL

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?

Best Answer

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.

Related Question