Number of pairs in SQL
Question
Suppose you're given the following table showing sock inventory. Each row represents an individual sock (with the same style/size), and the 'color' column denotes the sock's color. Given this information, write a SQL query to return the number of pairs (two socks with matching colors) that can be made from the inventory shown. Your result should be returned as a single integer number.
Table: color_sorting
| sock_id | color |
|---|---|
| 1 | red |
| 2 | blue |
| 3 | black |
| 4 | blue |
| 5 | red |
| 6 | black |
| 7 | red |
| 8 | white |
| 9 | red |
| 10 | blue |
| 11 | red |
| 12 | red |
You can view/query the data in an interactive SQL fiddle here.