SELECT c1.Age,
CASE
WHEN (SELECT COUNT (DISTINCT c2. Product) FROM “data” c2 WHERE c2. Age = c1. Age) = 1 THEN MAX (c1. Product)
ELSE ‘*’
END
FROM “data” c1
GROUP BY c1. Age
When I’m using this query, getting the error “index (8) must be less than size (3)”.How can i solve this problem.
I’m not sure what that index error means exactly, but also not sure why you need a self-join. Have you tried something like this:
SELECT Age,
CASE
WHEN (SELECT COUNT (DISTINCT Product)) = 1 THEN MAX (Product)
ELSE ‘*’
END
FROM “data”
GROUP BY Age
Or are the two “data” tables different tables? If it’s actually two different tables, maybe something like this would work:
SELECT c1.Age,
CASE WHEN COUNT(DISTINCT c2.Product) = 1 THEN MAX (c1.Product) ELSE ‘*’ END
FROM data1 c1 JOIN data2 c2 ON c1.Age = c2.Age
GROUP BY c1.Age
Otherwise, I feel like the original query is almost like trying to use cursors - doing a subselect for each row of a select, and it’s a bit confusing to me.
Explicit JOIN just does the whole join up front. Which isn’t great, but I’m not sure about the cursor-like approach and how to get it working.
Maybe someone else will know, or be able to.
Also, John Kowtko had another approach he mentioned (but he’s having trouble posting it here):
Assuming the purpose of this query is to list a bunch of product ages, and either display the single product at that age, or ‘*’ if there are multiple products at that age … then this query should work:
Select Age, case when count(distinct Product) = 1 then string_agg(Product else,‘,’) else ‘*’ end as Product group by Age
the string_agg is a placebo aggregate function that will display the product without additional formatting around it if there is only one item in the group.