← All Kits · SQL Kit

One Cell, Many Values: When a Column Holds a List

The column shape that makes DISTINCT lie to you · Part of the Analyst Prep Kit

By Michael Nocito, data analyst · Updated August 19, 2026

You ask a table for its list of categories, and the answer is 2,923. You were expecting about thirty. Nothing errored, nothing warned you, and the number is not a bug. It is the correct answer to a question you did not mean to ask. This happens when a column stores a list inside a single cell, and it quietly breaks DISTINCT, COUNT, and GROUP BY all at once. Here is how to spot it in under a minute, why the number came back wrong, and three ways to work with the column, easiest first.

The worked example throughout is a public Steam games dataset of 125,855 rows with a Genres column. Every number below is a real result from it, including the wrong one.

The symptom: a distinct count that is far too big

The opening move on any new column is to ask what values it holds:

SELECT COUNT(DISTINCT Genres) AS genre_count
FROM games_raw;

The answer came back 2,923. A digital storefront does not have 2,923 genres. It has a menu of a few dozen that a developer picks from.

That gap between the number you got and the number you expected is the single most valuable signal in this whole guide. It is not an inconvenience to route around, it is the finding. Whenever a distinct count on a categorical column comes back in the thousands, stop, because the column is almost certainly not holding what you think it is.

Look at the values before you count them

Before theorising, look. A count tells you how many, never what:

SELECT DISTINCT Genres
FROM games_raw
LIMIT 20;

There it is. Some cells hold one genre and some hold three, separated by commas. The column is not storing a genre, it is storing a list of genres as a piece of text.

Measure how widespread it is

Twenty rows is an impression. Turn it into evidence, because the answer decides how much work you are in for. If a handful of rows hold lists, that is a small cleanup. If nearly all of them do, the shape of the column is the problem:

SELECT COUNT(DISTINCT Genres) AS values_with_a_comma
FROM games_raw
WHERE Genres LIKE '%,%';

2,898 of the 2,923 distinct values contain a comma. That is 99 percent. This is not an edge case in the data, it is the data.

(LIKE '%,%' reads as "anything, then a comma, then anything." If that syntax is new, LIKE and Wildcards covers it.)

Why DISTINCT gave you the wrong answer

DISTINCT did exactly what it promises. It returned each different value in the column once. The problem is that a "value" here is a whole comma separated string, and it has no idea the commas mean anything:

Three rows in your result, and the word Indie is in all three. So 2,923 is not the number of genres. It is the number of unique combinations of genres that at least one game uses. That is a real fact about the data, it just is not the fact anybody asked for.

The same distortion runs through every aggregate you build on the column. GROUP BY Genres makes 2,923 groups, most with a handful of games in them. A bar chart of it is unreadable. A percentage share computed from it is wrong, because a game tagged with three genres is counted once, in a bucket of its own, rather than once per genre.

The values that never appear alone

Here is the trap that catches people who think they have solved it. Flip the comma filter around and you get the cells holding exactly one genre, which are genre names standing on their own:

SELECT DISTINCT Genres
FROM games_raw
WHERE Genres NOT LIKE '%,%'
ORDER BY Genres;

That returns 24 rows, and they look right. Action, Adventure, Casual, Indie, RPG, Racing, Simulation, Sports, Strategy, and so on. It is tempting to call that the genre list and move on.

It is incomplete, and nothing in the result says so. A value that is only ever applied alongside another one will sit inside the 2,898 lists and never once appear on its own. Probe for one:

SELECT DISTINCT Genres
FROM games_raw
WHERE Genres LIKE '%Gore%';

132 combinations come back, and not one of them is Gore by itself. Gore is real, it is used, and the 24 row list missed it completely. Splitting the column properly turns up 33 distinct values, so nine of them never stand alone.

Three ways to work with it, easiest first

SELECT Name, Price
FROM games_raw
WHERE Genres LIKE '%RPG%';

SELECT Name, Price
FROM games_raw
WHERE ',' || Genres || ',' LIKE '%,RPG,%';

SELECT g.genre, COUNT(*) AS games
FROM genre_list AS g
JOIN games_raw AS r
  ON ',' || r.Genres || ',' LIKE '%,' || g.genre || ',%'
GROUP BY g.genre
ORDER BY games DESC;

WITH RECURSIVE split(genre, rest) AS (
  SELECT '', Genres || ','
  FROM games_raw
  WHERE Genres IS NOT NULL AND Genres <> ''
  UNION ALL
  SELECT substr(rest, 1, instr(rest, ',') - 1),
         substr(rest, instr(rest, ',') + 1)
  FROM split
  WHERE rest <> ''
)
SELECT trim(genre) AS genre,
       COUNT(*) AS games
FROM split
WHERE trim(genre) <> ''
GROUP BY lower(trim(genre))
ORDER BY games DESC;

Which one to pick

That last row matters and gets skipped. Once you split, a game tagged with three genres appears in three rows, so the genre counts add up to more than the number of games. Nothing is wrong, but a reader will assume the parts sum to the whole unless you tell them otherwise.

Write it down as a limitation

A multi value column is exactly the kind of thing that belongs in your write up rather than buried in a query. It changes what your numbers mean. Three lines cover it:

• What the column actually holds. "The Genres field stores a comma separated list, so a game can carry several genres."
• How you handled it. "Genres were split to one row per game per genre before counting."
• What that does to the totals. "Games with several genres are counted once in each, so genre counts sum to more than the 125,855 games."

There is a second thing worth flagging in this particular dataset, and it is the kind of detail that separates a real analysis from a tutorial. Those 33 values are not all genres. Gore, Violent, Nudity and Sexual Content are content warnings. Accounting, Photo Editing and Video Production are software categories, not games at all. The field mixes several kinds of label into one column, so "top genre" is a misleading headline unless you decide which of the 33 count and say which you dropped. Documenting Data Limitations covers writing that section without undermining your own work.

Why the data looks like this

It helps to know this is a known shape with a known name, not a mess somebody made by accident. A database is described as being in first normal form when every cell holds a single value. A column packing a list into one cell breaks that rule, and the textbook fix is a separate small table with one row per pairing:

With that table, counting per genre is an ordinary GROUP BY and none of this guide is needed. So why does the flat version keep turning up? Because analysts receive data rather than design it. Exports flatten. Spreadsheets get typed by hand. APIs return an array and whoever wrote the CSV joined it with commas. Survey tools write "select all that apply" answers into one field as a matter of course.

You will meet this column shape in tags, skills, categories, region lists, product options, and multi select survey questions. Once you have recognised it once, you recognise it everywhere, and the first thing you will do on any new categorical column is look at twenty rows before you count them.