Error in subquery in postgres

Question

I have the following table, customers_categorization:

customer__id    visit_month    cust_type
2170809793      0              lagger
2170809793      10             retained
2170809793      11             retained
2170809793      12             lagger
2170809793      17             lagger
2170809793      23             retained
2170809793      24             lost
2174188545      0              lost

I am trying to write a SQL query in Postgresql where I want to find the proportion of customers who returned by month:

Here is what I have so far:

select visit_month,
       count(select customer__id from customers_categorization where cust_type=’retained’)/count(customer__id) as retention_rate
    from customers_categorization
    group by 1;

I keep getting a "syntax error at or near "select". I am not sure why this isn't working. Does postgresql not accept subqueries with the select statement?

Any help would be awesome!

klin · Accepted Answer · 2018-06-14 16:30:18Z

1

Use the syntax of count() with filter:

select
    visit_month,
    count(customer__id) filter (where cust_type = 'retained') /
        count(customer__id) as retention_rate
from customers_categorization
group by 1;

or with case:

select
    visit_month,
    count(case when cust_type = 'retained' then customer__id end) /
        count(customer__id) as retention_rate
from customers_categorization
group by 1;

answered Jun 14, 2018 at 16:30

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Error in subquery in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related