CODEWITHSUNDEEP
postgresql
sparkle
sparkle

Reputation: 7400

Sub query in SELECT - ungrouped column from outer query

I have to calculate the ARPU (Revenue / # users) but I got this error:

subquery uses ungrouped column "usage_records.date" from outer query LINE 7: WHERE created_at <= date_trunc('day', usage_records.d... ^

Expected results:

Postgresql (Query)

SELECT 
    date_trunc('day', usage_records.date) AS day,
    SUM(usage_records.quantity_eur) as Revenue,
    ( SELECT 
        COUNT(users.id)
        FROM users 
        WHERE created_at <= date_trunc('day', usage_records.date)
    ) as users_count
    
FROM users 
INNER JOIN ownerships ON (ownerships.user_id = users.id) 
INNER JOIN profiles ON (profiles.id = ownerships.profile_id)
INNER JOIN usage_records ON (usage_records.profile_id = profiles.id)

GROUP BY DAY
ORDER BY DAY asc

Upvotes: 0

Views: 1537

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

your subquery (executed for each row ) cointain a column nont mentioned in group by but not involeved in aggregation .. this produce error but you could refactor your query using a contional also for this value

SELECT 
    date_trunc('day', usage_records.date) AS day,
    SUM(usage_records.quantity_eur) as Revenue,
    sum( case when  created_at <= date_trunc('day', usage_records.date) 
        AND users.id is not null
        then 1 else 0 end ) users_count
    
FROM users 
INNER JOIN ownerships ON (ownerships.user_id = users.id) 
INNER JOIN profiles ON (profiles.id = ownerships.profile_id)
INNER JOIN usage_records ON (usage_records.profile_id = profiles.id)

GROUP BY DAY
ORDER BY DAY asc

Upvotes: 2

Related Questions