I'm trying to create a query where if customers took 1 trip that day, then they would have to pay the price of a single ticket (200). If they took 2 trips on the day, then they would have to pay single * 2. And if they went on 3 or more, then they would have to pay for a day ticket (500). I managed to produce an output, but I don't understand why the query adds up the single tickets.

This is the

Topics Covered:

• Data Retrieval: The problem is about retrieving data from a database.
• Subqueries: It includes the use of subqueries to fetch ticket costs.
• Conditional Logic: The CASE statement is used to apply conditional logic to calculate costs based on the number of trips.
• Aggregation: The problem involves aggregating data using the COUNT() function to count the number of trips.
• Table Joins: JOIN is used to combine data from two different tables (CustomerTrip & BusTrip) based on a common column.
• Filtering Data: There are WHERE clauses used to filter data based on specific conditions.
• Grouping Data: GROUP BY is used to group data by customer ID and date.