Question 387331 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using field alias from main query in a subquery

I want to filter in subquery with aliased field from main query:

select a.created_at::date as creation_date, a.column as col1, 
       (select count(*)
        from table b
        where a.col1 = b.col1
          and a.created_at::date = b.created_at::date) as count_one 
from table a
group by creation_date, col1;

but this gives error
ERROR: subquery uses ungrouped column "a.created_at" from outer query

Any options how to solve it?

Tags:

2

Answers


  1. 0

    I don’t know the actual column types, but I do see the outer query casts the original created_at as a date, and then gives it a creation_date alias. Assuming this was originally a datetime (or similar), this means we have narrowed the range of the values from the source column.

    The outer query then does a GROUP BY using the alias for the narrowed column, meaning the GROUP BY MUST be grouping to the result of the cast, rather than with the source of the cast.

    Now we look at the inner/nested query, and see it’s trying to reference the original source column. But because of the GROUP BY in the outer query, this column no longer exists.

    The database has to resolve the groupings before it can resolve correlated subqueries. I know you’re trying to do the same cast, to get the same values, but for this it does not matter… from the perspective of the subquery, the original source column is no longer there.

    Login or Signup to reply.
  2. 0

    It’s unclear why this doesn’t work, because you are using the same value in the subquery as the group by. At the very least, it should work if you don’t use the alias and instead repeat the calculation group by a.created_at::date, a.column.

    Otherwise, you could use a lateral join for the group by value

    select
  v.creation_date,
  a.column as col1, 
  (
    select count(*)
    from table b
    where a."column" = b.col1
      and v.creation_date = b.created_at::date
  ) as count_one 
from table a
cross join lateral (values
  (a.created_at::date)
) v(creation_date)
group by
  v.creation_date,
  a."column";

    Note that casting in a where can be inefficient. You might be better off with

          and b.created_at >= v.creation_date
      and b.created_at <  v.creation_date + interval '1' day
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search