Re: Inserting a constant along with field values. - Mailing list pgsql-novice

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp@tcd.ie> writes:
> Now, what I would like to do is to put the COUNT(z.id) into a CTE and
> then essentially SELECT a constant value into that part of the query

> WITH cte AS
> (
>   SELECT COUNT(z.id) AS zcnt FROM z
> )
> SELECT
>               x.fields,
>               y.fields,
>               c.zcnt
> FROM xtab x JOIN ytab y ON  x.x_key = y.y_key
> JOIN cte c ON .......

That looks fine as far as it goes.

> I keep getting messages like "unknown column 'p.pcnt'".

There's no p.pcnt in what you showed, so it seems like maybe your
problem is somewhere else.

If you're trying to figure out what the join condition should be:
you don't need one, because the single row from "cte c" is supposed
to join to every row of the x/y join result.  So you could just
write "ON true".  Or leave it off altogether by writing CROSS JOIN,
or by using comma syntax, like

... FROM xtab x JOIN ytab y ON x.x_key = y.y_key, cte c

            regards, tom lane