Aaron Aaron - 6 months ago 90
MySQL Question

Creating a query where no join exists

I have a query that if there is a join, averages the difference between the created and canceled date. The query is below

select avg(TIMESTAMPDIFF(DAY, users.created_at,subscriptions.canceled_at)) as total from users
join subscriptions on users.id = subscriptions.user_id


What I am trying to do is if there is no join use NOW() instead off canceled_at and then averaging both mysql queries to get an average of the days. How would I do that?

Answer Source

A left join should do the trick:

SELECT   AVG(TIMESTAMPDIFF(DAY, 
                           users.created_at, 
                           COALESCE(subscriptions.canceled_at, NOW())) AS total
FROM      users
LEFT JOIN subscriptions ON users.id = subscriptions.user_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download