Sock sorting in SQL
Question
You're given the following table showing the data output of a machine designed to determine a sock's color. You'll notice there can be multiple records per sock. Assuming there are only two possible colors for the socks (red and blue), write a SQL query to count the number of socks for each color such that any sock that has been flagged both red and blue by the machine is sorted into blue, any sock with just a blue flag is sorted into blue, and any sock with just a red flag is sorted into red.
Table: color_sorting
| sock_id | color |
|---|---|
| 1 | red |
| 1 | blue |
| 1 | blue |
| 2 | blue |
| 3 | red |
You can view/query the data in an interactive SQL fiddle here.