How can I aggregate counts of specific values in an array (SQL)?

user_17867294 0
Viewed 114

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.

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.

Here is a crude example:

| Color Combination (array) |

| [Red],[Blue],[Yellow] |

| [Blue],[Green],[Purple] |

| [Yellow],[Blue] |

So my goal is to eventually get to an output like this:

| Color | # of Times Found |

| Yellow | 2 |

| Blue | 2 |

| Red | 1 |

| Green | 1 |

| Purple | 1 |

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.

presto amazon-athena amazon-web-services sql
1 Answers

You can use unnest to expand the array column:

-- sample data
WITH dataset (colors) AS (
    VALUES (array [ 'Red', 'Blue', 'Yellow' ]),
        (array [ 'Yellow', 'Orange' ]),
        (array [ 'Green' ])
)

--query
select color, count(*) times
from dataset
cross join unnest (colors) t(color)
group by color
order by 2 desc -- to prettify output

Output:

color times
Yellow 2
Red 1
Blue 1
Orange 1
Green 1
user_25012790
Related