I have a SQL table to store trip books. This table contains (among others) the following fields:
'trip_id' int(10) --> Foreign key to trip table
'departure_id' int(10) --> Foreign key to departure table
'book_category_id' int(10) --> Foreign key to type_of_book table
'customer_id' int(10) --> Foreign key to customer table
A book is defined by a trip and a departure, and it's included into a category. I need to count how many books there are for each category.
In the following example
trip_id departure_id book_category_id customer_id
------- ------------ ---------------- -----------
1 1 1 1
1 2 2 2
2 1 1 3
1 1 2 4
2 1 1 5
there would be 5 books in total:
- "Trip 1" (trip_id=1 and departure_id=1) has 1 book of category 1 and 1 book of category 2
- "Trip 2" (trip_id=1 and departure_id=2) has 1 book of category 2
- "Trip 3" (trip_id=2 and departure_id=1) has 2 books of category 1
I've tried running the following query:
SELECT `books`.`trip_id`, `books`.`departure_id`, COUNT(*) AS total_books
FROM (`books`)
GROUP BY `books`.`departure_id`
and, obviusly, I only get the total books for each trip:
trip_id departure_id total_books
------- ------------ -----------
1 1 2
1 2 1
2 1 2
How could I get a column showing the total books for category 1 and another column showing the total books for category 2?
trip_id departure_id total_books_category_1 total_books_category_2
------- ------------ ---------------------- ----------------------
1 1 1 1
1 2 0 1
2 1 0 2
Thanks in advance :)
