For the exercise #7 in the link above, the recommended code under the hint section below,
SELECT CASE
WHEN medium LIKE ‘%gold%’ THEN ‘Gold’
WHEN medium LIKE ‘%silver%’ THEN ‘Silver’
ELSE NULL
END AS ‘Bling’,
COUNT(*)
FROM met
WHERE Bling IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;
would yield the following results is derived.
Silver - 293
Gold - 41
However, Within the count of 41 under Gold, there exists a record with ‘Gold, silver, platinum, black opals, boulder opals, demantoid garnets, rubies, enamel.’ under the ‘medium’ column, which contains both words Gold and Silver.
I am not quite sure how the above record got “thrown” into the Gold category despite having both Gold and Silver in it. Would anyone be able to shed some light on this?
A CASE statement goes through the conditions and returns a value once it’s met then goes on to the next condition. So, in your query CASE WHEN medium LIKE '%gold%' THEN 'Gold'
“gold” is the first value in that description, so that condition is met.
Is there a way to write the code so that it can return both the correct number of pieces that are gold and the correct number of pieces that are silver? (counts descriptions with gold and silver into both categories)
Maybe it’s just easier to run the code twice, once per condition.
Using the case code they’ve given, the “gold” and “silver” label will change counts depending on which comes first, I was wondering of a way where I could grab the total number of gold items, and the total number of silver items.
After writing a set of code that didn’t work, I ended up with the following:
SELECT CASE
WHEN medium LIKE ‘%silver%’ THEN ‘Silver’
ELSE NULL
END AS ‘Bling’,
COUNT(*)
FROM met
WHERE Bling IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;
SELECT CASE
WHEN medium LIKE ‘%gold%’ THEN ‘Gold’
ELSE NULL
END AS ‘Bling’,
COUNT(*)
FROM met
WHERE Bling IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;
However, I was wondering if there’s a simpler way to do this.
The first thing that springs to mind is using a nested CASE statement so that you’d have a filter more like gold_items, gold_and_silver_items and then silver_items. That doesn’t quite fit what you ask since COUNT is based on the number of matching rows (you have fewer silver and gold pieces but the same total because 3 of them match both) but it does make more sense in terms of counting.
But the question asks, “Count the number of pieces where the medium contains ‘gold’ or ‘silver’.”
The items don’t have to be 100% gold or 100% silver, they just have to contain either.
As I wrote above, CASE goes through the conditions and will return a value once it’s met, then it goes on to the next condition. So, let’s say one piece is made of gold, wood, marble. The CASE condition is met b/c “gold” is in the description so that’s one, then it moves onto the next row, etc.
If you want to get only items that just had gold and just had silver in them you’d have to break out the medium column for pieces that had more than one medium. You’d have to alter the table. Maybe the column could be called “composite” and those rows that had items with multiple mediums in the piece would be assigned to it.
But, that would be an inaccurate count and a lot more than was requested.
I think this is going beyond the questions a little so I don’t think there’s a right/wrong answer (I suppose task 7 is probably best just using OR operator) but I believe the user meant that in the extended hint the order of the query means that mixed mediums (gold AND silver) get mixed into the count of either gold OR silver based on the order of the case which is an interesting point. It’s not part of the question really but it’s still interesting.
Ignoring other mediums (not excluding, just ignoring) except silver and gold there are-
293 silver (contain no gold) items
38 gold (contain no silver) items
3 gold and silver items
My first thought was that those numbers can be obtained relatively easily with a nested case. As you say getting those values with a true mixed number would be more difficult and nothing immediately springs to my mind short of subqueries and temporary tables-
296 items contains silver (inclusive)
41 items contain gold (inclusive)
Perhaps there’s an easier route, either way it’s a more interesting task if anyone feels like doing it.
