I have a dataset showing the market cap of the stocks in my portfolio. When I run my query, I would like to replace the number with either Small , Mid or Large based on the size. For Example, my API feed will show:
when market_cap is 0-9999 = replace with 'small', 1000-99999 = replace with 'Mid', 100000+ = replace with 'large" Not sure how to put this into the query though.
A case expression is obviously the way to go. I would phrase this as:
SELECT t.*,
(CASE WHEN Market_Cap < 1000 THEN 'Small'
WHEN Market_Cap < 10000 THEN 'Mid'
ELSE 'Large'
END)
FROM t;
This uses the fact that case condition stop at the first match, so between is not necessary. This particularly construct assumes that market_cap is never NULL or negative.
You can use the case statement here:
SELECT
Ticker,
Company,
Sector,
Industry,
Country,
Market_Cap,
CASE
WHEN Market_Cap >= 0 AND Market_Cap <= 999 THEN 'Small'
WHEN Market_Cap > 999 AND Market_Cap <= 9999 THEN 'Medium'
WHEN Market_Cap > 9999 THEN 'Large'
END
FROM
tblPortfolio
Market_Cap value of 999.55 ?? The values shown by the OP aren't integer values - they're regular decimals - and your code doesn't handle that very well....
SELECT
*,
CASE
WHEN Market_Cap BETWEEN 0 AND 999 THEN 'Small'
WHEN Market_Cap BETWEEN 1000 and 9999 THEN 'Mild'
WHEN Market_Cap >= 10000 THEN 'Large'
ELSE 'no Market_Cap range assigned' END AS Category
FROM
[table name]
