CODEWITHSUNDEEP
mysqlsql
Or Weinberger
Or Weinberger

Reputation: 7472

Whats wrong with this MySQL query?

SELECT STOCK_SYMBOL FROM daily
WHERE DAILY_DATE > '2011-03-01 23:59:59'
GROUP BY STOCK_SYMBOL HAVING MAX(DAILY_CHANGE_PERCENT) < -0.1

This is my structure:

STOCK_ID    STOCK_SYMBOL    DAILY_DATE            DAILY_CHANGE_PERCENT
1           GOOG            2011-03-03 01:01:01   -0.300

I'm trying to get only the stocks that for every date within that date range, DAILY_CHANGE_PERCENT is lower than -0.1

at the moment i'm getting mixed results.

Example of a valid result:

STOCK_ID    STOCK_SYMBOL    DAILY_DATE            DAILY_CHANGE_PERCENT
1           GOOG            2011-03-03 01:01:01   -0.300
1           GOOG            2011-03-04 01:01:01   -0.110
1           GOOG            2011-03-05 01:01:01   -0.500
1           GOOG            2011-03-06 01:01:01   -0.200

Thanks,

Upvotes: 2

Views: 102

Answers (3)

Adam Lukaszczyk
Adam Lukaszczyk

Reputation: 4926

What data type you have of DAILY_CHANGE_PERCENT column? if this is varchar or so the lower-than won't work.

Otherwise - I look at your query and it seems to be OK.

UPDATE

Or try this one:

SELECT STOCK_SYMBOL 
FROM daily
GROUP BY STOCK_SYMBOL 
HAVING MAX(DAILY_CHANGE_PERCENT) < -0.1 AND DAILY_DATE > '2011-03-01 23:59:59'

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

To get your "valid result" with all columns, is this what you used?

SELECT daily.STOCK_ID, STOCK_SYMBOL, DAILY_DATE, DAILY_CHANGE_PERCENT
FROM daily
join (
    SELECT STOCK_ID
    FROM daily
    WHERE DAILY_DATE > '2011-03-01 23:59:59'
    GROUP BY STOCK_ID
    HAVING MAX(DAILY_CHANGE_PERCENT) < -0.1
) X ON X.STOCK_ID = daily.STOCK_ID
WHERE DAILY_DATE > '2011-03-01 23:59:59'

I swapped to stock_id in case there are duplicate symbols.
Notice also that I use the date filter twice.

Upvotes: 1

Tim
Tim

Reputation: 5421

You want the distinct set of stock symbols where there is any drop in price duing the specified period AND you want to remove from that set of stock symbols any stock whose price went up on any day in the specified period. Simply looking for a maximum drop of less than 'x' does not rule out stocks that may have gone up at some point.

Upvotes: 0

Related Questions