Postgres - Subtraction of two queries

Question

I want to make a subtraction like this:

SELECT 
    (SELECT ST_Y(ST_Centroid(ST_Collect(column1))) AS distance_km 
     FROM table1) - 
    (SELECT ST_Y(ST_Transform(column1,4326)) AS distance_km 
     FROM table1) AS Difference

But the ST_Y(ST_Centroid(ST_Collect(column1))) has only one row and ST_Y(ST_Transform(column1,4326)) has multiple rows. I want to subtract the multiple rows by the column and see the results for each multiple row. With this query I get:

ERROR: more than one row returned by a subquery used as an expression

Any help?

Solution

The following query will join a one-row result set against a many row result set, and allow them to be compared.

SELECT
    a.*,
    b.*,
    a.distance_km - b.distance_kn    AS difference
FROM
(
    SELECT  ST_Y(ST_Centroid(ST_Collect(column1))) AS distance_km FROM table1
)
    a
CROSS JOIN
(
    SELECT ST_Y(ST_Transform(column1,4326)) AS distance_km FROM table1
)
    b

Comments

[MatBailie]

@PRVS - Sub-queries and CROSS JOIN are ANSI-SQL. So, yes, it works in PostGreSQL.

[a_horse_with_no_name]

@MatBailie: the name is either spelled PostgreSQL or just Postgres. Never with a capital G