PostgreSQL "Subquery must return only one column" error

Question

Hello i'm trying to do a cohort study. I have some trouble about a subquery error when running my query. I actually can compute the repeat percentage only, but when i add the number of new customers and the number of repeaters, the error came in. I want to have the details of this percentage (the ratio of repeaters over the number of new customers) in my final result.

Thank you very much for your help ! :)

Line 24-25-26

SELECT time_table.*,
(
    WITH new_customers AS
    (
        SELECT DISTINCT
                order_report._customer_id
        FROM order_report

        INNER JOIN
        (
              SELECT DISTINCT _customer_id
              FROM order_report
              WHERE order_report._created_at::timestamp BETWEEN time_table.first_order_start AND time_table.first_order_stop
              AND _order_status = 'paid' AND _order_product_status != 'UNAVAILABLE'
        ) AS period_orders ON period_orders._customer_id = order_report._customer_id

        WHERE _order_status = 'paid' AND _order_product_status != 'UNAVAILABLE'
        GROUP BY order_report._customer_id
        HAVING MIN(order_report._created_at::timestamp) BETWEEN time_table.first_order_start AND time_table.first_order_stop
    )
    SELECT
        COUNT(*) as repeaters,
        (SELECT COUNT(*) FROM new_customers) as new_customers,
        COUNT(*)::float/(SELECT COUNT(*) FROM new_customers) as repeat_percent
        FROM
        (
            SELECT COUNT(*), order_report._customer_id
            FROM order_report
            INNER JOIN new_customers
            ON new_customers._customer_id = order_report._customer_id
            WHERE order_report._created_at::timestamp <= time_table.stop
            AND _order_status = 'paid' AND _order_product_status != 'UNAVAILABLE'
            GROUP BY order_report._customer_id
            HAVING COUNT(*) > 1
        ) AS REPEATS
)
FROM
(
    WITH time_serie AS
    (
        SELECT
            generate_series AS start,
            (generate_series + interval '3 month' - interval '1 second') AS stop
        FROM generate_series('2017-01-01 00:00'::timestamp, '2017-06-30', '1 month')
    ),
    first_order_serie AS
    (
        SELECT
            start AS first_order_start,
            stop AS first_order_stop
        FROM time_serie
    )
SELECT * FROM time_serie, first_order_serie) AS time_table

Possible duplicate of [error : subquery must return only one column](https://stackoverflow.com/questions/19649112/error-subquery-must-return-only-one-column) — Lucas, Jul 24 '19 at 09:31

score 0 · Answer 1 · answered Jul 24 '19 at 10:39

I think you should divide queries and you inspect one by one. And then you detect which query is wrong. If you dedicate it, you share it again. I think your problem maybe this query:

-------------------
FROM
(
    WITH time_serie AS
    (
        SELECT
            generate_series AS start,
            (generate_series + interval '3 month' - interval '1 second') AS stop
        FROM generate_series('2017-01-01 00:00'::timestamp, '2017-06-30', '1 month')
    ),
    first_order_serie AS
    (
        SELECT
            start AS first_order_start,
            stop AS first_order_stop
        FROM time_serie
    )

score 0 · Answer 2 · answered Jul 24 '19 at 11:07

0

Your query is starting with a select, so everything after that is a subquery.

Write your query with all the CTEs first:

with new_customers as (
      . . .
     ),
     time_serie as (
     ),
     first_order_serie as (
     )
select . . .
from . . .

answered Jul 24 '19 at 11:07

Gordon Linoff

1,242,037
58
646
786

PostgreSQL "Subquery must return only one column" error

2 Answers2