How to write an SQLite query that uses 4 tables and count()?

Question

I've got 4 tables that I want data from.

Trip_T
  tripID (PK)
  userID (FK)
  ...

User_T
  userID (PK)
  username
  ...

Excursion_T
  excursionID (PK)
  tripID (FK)
  ...

POI_T
  poiID (PK)
  excursionID (FK)
  ...

I want to create a table with one row for each trip in the db. Each row should include the tripID, title, the user's name associated with the trip, the number of excursions made on the the trip and the number of poi (points of interest) associated with those excursions.

I'm using the following query:

SELECT Trip_T.tripID, Trip_T.title, User_T.username 
COUNT(DISTINCT Excursion_T.excursionID) AS numExcursions, 
COUNT(DISTINCT POI_T.poiID) AS numPOI
FROM Trip_T
INNER JOIN User_T ON User_T.userID = Trip_T.userID
INNER JOIN Excursion_T ON Excursion_T.tripID = Trip_T.tripID
INNER JOIN POI_T ON POI_T.excursionID = Excursion_T.excursionID

Even though I have multiple trips in the db, each with multiple excursions and pois, the query returns 1 row with what looks like the total number of excursions and total number of pois for all trips.

Any help is appreciated.

Answer 1

You forgot to add grouping to your query:

GROUP BY Trip_T.tripID, Trip_T.title, User_T.username

This way the counters correspond to each triplet of (Trip_T.tripID, Trip_T.title, User_T.username)