Thread: Referencing subselect returned value

I'd like to reference values returned by a subselect in the same SELECT
query.

Example:

SELECT id,
  ( SELECT COUNT(*) FROM second ) AS value_to_reference,
  ( value_to_reference + 1 ) AS value_that_uses_referenced_one
FROM first;

Any suggestions?

Thank you,
Marco

On Tue, Jan 20, 2004 at 21:21:56 +0100,
  Marco Lazzeri <marcomail@noze.it> wrote:
> I'd like to reference values returned by a subselect in the same SELECT
> query.
>
> Example:
>
> SELECT id,
>   ( SELECT COUNT(*) FROM second ) AS value_to_reference,
>   ( value_to_reference + 1 ) AS value_that_uses_referenced_one
> FROM first;
>
> Any suggestions?

If you want to use a subselect repeatedly, you can either repeat it or
join it. Doing a join will normally be better.

Repeated select example:
SELECT id,
  ( SELECT COUNT(*) FROM second) AS value_to_reference,
  ( SELECT COUNT(*) FROM second)+1 AS value_to_reference_one,
FROM first;

Join example:
SELECT id,
  cnt AS value_to_reference,
  cnt+1 AS value_reference_one,
FROM first, (select COUNT(*) AS cnt from second) AS third;