title: How can I aggregate counts of specific values in an array (SQL)?
body: <p>I have one field that can hold up to 15 different values in an array.  There are about 90 different values that can be stored for those values.</p>
  <p>The eventual output I am looking to get is one row for each value with a column for the name of the value and the count of that value within that array.</p>
  <p>Here is a crude example:</p>
  <p>| <strong>Color Combination (array)</strong> |</p>
  <p>| [Red],[Blue],[Yellow]     |</p>
  <p>| [Blue],[Green],[Purple]   |</p>
  <p>| [Yellow],[Blue]                 |</p>
  <p>So my goal is to eventually get to an output like this:</p>
  <p>| <strong>Color</strong> | <strong># of Times Found</strong> |</p>
  <p>| Yellow    | 2                    |</p>
  <p>| Blue      | 2                    |</p>
  <p>| Red       | 1                    |</p>
  <p>| Green     | 1                    |</p>
  <p>| Purple    | 1                    |</p>
  <p>Any insights would be super helpful (not very familiar with arrays in SQL), I started with making a case statement to see if it contains the variable and then made columns for each variable with 1s if it is found, but this doesn't do me much good when I need to order the # of times found since I end up with 90 columns and 1 row with the total number of times that is found.</p>