I need to return a value in the column only if the the count is greater than 1 of that same column.
Example table:
| FOOD_TYPE | ITEM | COST_TO_BUY | SELL_PRICE |
|---|
| Ice Cream | Chocolate | 0.50 | 2.00 |
| Ice Cream | Vanilla | 0.50 | 2.00 |
| Ice Cream | Strawberry | 0.50 | 2.00 |
| Chicken | Wings | 2.00 | 5.00 |
| Beef | Steaks | 8.00 | 15.00 |
| Beef | Roast | 10.00 | 18.00 |
In this example I want to select the FOOD_TYPE, ITEM, SUM(COST_TO_BUY) AND SUM(SELL_PRICE), but I only want to select the ITEM if there are more than 1 items in the FOOD_TYPE.
I have tried all manners of grouping and even including the a case statement in the select as follows:
CASE
WHEN (COUNT(DISTINCT ITEM) > 1)
THEN ITEM
ELSE ''
END
However, this is negated when I do the grouping because it forces me to use ITEM as a group and I cannot use the whole case statement in the GROUP BY because it has an aggregate function.
Please help!