Why does adding GROUP BY cause a seemingly unrelated error?-CodePudding

The following code works fine:

SELECT name, (SELECT count(item_id) FROM bids WHERE item_id = items.id)
FROM items;

However, when I add

SELECT name, (SELECT count(item_id) FROM bids WHERE item_id = items.id)
FROM items
GROUP BY name;

I get ERROR: subquery uses ungrouped column "items.id" from outer query

Can anyone tell me why this is happening? Thanks!

CodePudding user response：

If you GROUP BY name then any other columns you select from items must have an aggregate function applied. That's what GROUP BY means.

In your case, you are using another column from items -- id -- in a correlated scalar subquery. That's not an aggregate function, and id is not in the GROUP BY clause, so you get an error.

You could instead GROUP BY name, id. That should give you the same results as the first query, and is probably pointless.

If you actually have multiple rows in items with the same value for name, and you want to group the results of the scalar subquery for those values, you need to specify how to group them. Perhaps you want the total of the subquery results for each value of name. If so, I think you could do:

SELECT name, SUM(SELECT count(item_id) FROM bids WHERE item_id = items.id))
FROM items
GROUP BY name;

(I'm not positive about the specific syntax as I don't have a Postgres instance to test against.)

A clearer way to express it might be:

SELECT name, SUM(bid_count)
FROM (
SELECT name, (SELECT count(item_id) FROM bids WHERE item_id = items.id) AS bid_count
FROM items
)
GROUP BY name

CodePudding user response：

Join the tables then perform the GROUP BY:

select i.name, count(b.item_id)
  from items i
  inner join bids b
    on b.item_id = i.id
  group by i.name

db<>fiddle here