CODEWITHSUNDEEP
postgresql
AGuyHasNoName
AGuyHasNoName

Reputation: 109

Error in subquery in postgres

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!

Upvotes: 1

Views: 236

Answers (1)

klin
klin

Reputation: 121889

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;

Upvotes: 1

Related Questions