I need help in writing a subquery-CodePudding

I have a query like this to create date series:

Select month
    From
    (select to_char(created_date, 'Mon') as Month,
           created_date::date as start_day,
           (created_date::date   interval '1 month - 1 day ')::date as end_day
    from generate_series(date '2021-01-26', 
                         date '2022-04-26', interval '1 month') as g(created_date)) AS "thang"

And the table looks like this:

month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

Now I want to count the status from the KYC table.

So I try this:

Select
    (Select month
    From
    (select to_char(created_date, 'Mon') as Month,
           created_date::date as start_day,
           (created_date::date   interval '1 month - 1 day ')::date as end_day
    from generate_series(date '2021-01-26', 
                         date '2022-04-26', interval '1 month') as g(created_date)) AS "thang"),
    count(*) filter (where status = 4) as "KYC_Success"
From kyc
group by 1

I hope the result will be like this:

Month |  KYC_Success
Jan   |      234
Feb   |      435
Mar   |      546
Apr   |      157

But it said

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

What should I change in this query?

CodePudding user response：

Let us assume that the table KYC has a timestamp column called created_date and the status column, and, that you want to count the success status per month - even if there was zero success items in a month.

SELECT kyc.month
    , count(CASE WHEN kyc.STATUS = 'success' THEN 1 END) AS successes
FROM (
    SELECT to_char(created_date, 'Mon') AS Month
        , created_date::DATE AS start_day
        , (created_date::DATE   interval '1 month - 1 day ')::DATE AS end_day
    FROM generate_series(DATE '2021-01-26', DATE '2022-04-26', interval '1 month') AS g(created_date)
    ) AS "thang"
LEFT JOIN ON kyc ON kyc.created_date>= thang.start_date
    AND kyc.created_date < thang.end_date 
GROUP BY kyc.month;