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 — Lucas
– Lucas, Commented Jul 24, 2019 at 9:31

Mustafa BYKSY · Accepted Answer · 2019-07-24 10:39:08Z

0

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
    )

answered Jul 24, 2019 at 10:39

Mustafa BYKSY

939 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-07-24 11:07:20Z

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, 2019 at 11:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

PostgreSQL "Subquery must return only one column" error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related