Names in our data are unique. Can this still happen?

Product names, company names, and cities repeat even when customer names do not. The same query shape, GROUP BY the label, merges every row that shares the text.

The AI showed an id as well. Does that make it safe?

Only if the GROUP BY includes the id. Selecting MIN(customer_id) while grouping by name still merges the people, and the id you see is just one of them.

What about two rows for the same person, after a rename?

Grouping by name splits one person into two rows. Grouping by id keeps them together, and MIN(name) shows one of the names. If the person renamed, say that the name is one of several.

Is this the join that doubles the revenue?

No. A fan-out join repeats rows because the other table matched more than once. Here each order is counted once. Two different customers are added into one bucket because the text matched.

Should the AI refuse to group by name?

No. 'Revenue by city' or 'orders by plan name' is a real question when you mean the label. The miss is using the label as a stand-in for a person, product, or account that has its own id.

GROUP BY Name Not ID | Duplicate Names

You ask: "Who is our top customer?" The AI says Alex Kim, $5,100.

SELECT customer_name, SUM(amount) AS revenue
FROM orders
GROUP BY customer_name
ORDER BY revenue DESC
LIMIT 1

There are two customers named Alex Kim. One has $4,200 of orders, the other $900. The query adds them. The biggest single customer is Jordan Lee at $4,800, who now ranks second.

Every order is real. The total of the whole table is right. The row labeled "Alex Kim" is two people.

The name is a label, the id is the customer

GROUP BY collapses whatever you give it. customer_name collapses text. customer_id collapses accounts.

-- one row per account, name shown beside it
SELECT customer_id,
       MIN(customer_name) AS customer_name,
       SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC

MIN(customer_name) is there so the name can be selected under a strict GROUP BY. It does not decide who gets merged. The id does.

This is not a join repeating rows. Count the orders in the result and it matches the table. The mistake is the bucket, the same family as grouping by a timestamp and calling it a day: the key is more fine or more coarse than the thing you asked about.

It shows up anywhere the AI groups by the column it can read: product name instead of product id, company name instead of account id, city instead of store id.

How to catch it

SELECT customer_name, COUNT(DISTINCT customer_id) AS accounts
FROM orders
GROUP BY customer_name
HAVING COUNT(DISTINCT customer_id) > 1
ORDER BY accounts DESC

Any row here was merged in the top-customer query. If this returns nothing, grouping by name happened to be safe on this table. It is still the wrong key the next time two people share one.

The prompt that heads this off

When I ask for top customers, products, or accounts, group by the id,
not the name. Show the name next to it. If one name belongs to more
than one id, tell me instead of merging them. Use the name alone only
when I ask for the label itself, such as city or plan name.

The short version

GROUP BY customer_name adds everyone who shares the text. Group by the id, show the name beside it, and check whether any name maps to more than one id before you rank them.

Read-only MCP gateway: mcpserver.design.

Related reading: