CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Wazery
Wazery

Reputation: 15873

How to have SQL query with 2 subqueries divided

I have a database which has these tables:

  1. Users (id, email)
  2. Trips (id, driver_id)
  3. MatchedTrips (id, trip_id)

I need to get for each user the total number of trips he created divided by the total matches found.

I am stuck in building the raw SQL query for this. Here is what I tried, and sure it's far from being correct.

SELECT
  users.email,
  total_trips.count1 / total_matches.count2
FROM users CROSS JOIN (SELECT
        users.email,
        count(trips.driver_id) AS count1
      FROM trips
        INNER JOIN users ON trips.driver_id = users.id
      GROUP BY users.email) total_trips
      CROSS JOIN (SELECT users.email, count(matches.trip_id) AS count2
                   FROM matches
                   LEFT JOIN trips ON matches.trip_id = trips.id
                   LEFT JOIN users ON trips.driver_id = users.id
                   GROUP BY users.email) total_matches;

Upvotes: 2

Views: 459

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The simplest way is probably to use count(distinct):

select u.email,
       count(distinct t.id) as num_trips,
       count(distinct m.id) as num_matches,
       (count(distinct t.id) / count(distinct m.id)) as ratio
from users u left join
     trips t
     on t.driver_id = u.id left join
     matches m
     on m.trip_id = t.trip_id
group by u.email;

Note: If emails are unique, then the query can be simplified. count(distinct) can be expensive under some circumstances.

Upvotes: 1

klin
klin

Reputation: 121604

You can calculate total trips and total matches for each driver in the way like this:

select driver_id, count(t.id) as total_trips, count(m.id) as total_matches
from trips t
left join matches m on (t.id = trip_id)
group by 1

Use this query as a derived table in join with users:

select email, total_trips, total_matches, total_trips::dec/ nullif(total_matches, 0) result
from users u
left join (
    select driver_id, count(t.id) as total_trips, count(m.id) as total_matches
    from trips t
    left join matches m on (t.id = trip_id)
    group by 1
    ) s on u.id = driver_id
order by 1;

SQLFiddle.

Upvotes: 2

Related Questions