Question 278316 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Finding the right ticket cost

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 database that I’m working on. It’s quite a big one, so I used dbfiddle instead of typing it all out here.

This is the query I tried.

SELECT ct.c_id, DATE(bt.start_time) AS date,
SUM(
    CASE
        WHEN tc.duration = 'Single' AND trip_count = 1 THEN tc.cost
        WHEN tc.duration = 'Single' AND trip_count = 2 THEN tc.cost * 2
        ELSE 0
    END
) +
SUM(
    CASE
        WHEN tc.duration = 'Day' AND trip_count >= 3 THEN tc.cost
        ELSE 0
    END
) AS total_cost
FROM CustomerTrip ct JOIN BusTrip bt ON ct.b_id = bt.b_id
JOIN TicketCosts tc ON tc.duration IN ('Single', 'Day')
JOIN(
    SELECT
        b_id,
        DATE(start_time) AS trip_date,
        COUNT(*) AS trip_count
    FROM BusTrip GROUP BY b_id, DATE(start_time)) 
    trip_counts ON ct.b_id = trip_counts.b_id AND DATE(bt.start_time) = trip_counts.trip_date
GROUP BY ct.c_id, DATE(bt.start_time);
Tags:

2

Answers


  1. 0

    Make the aggregation of tips an inner subquery, and the once aggregated use subqueries in the result set to calculate cost:

    SELECT c_id, date, trips,
                   least(
                           (SELECT cost
                            FROM TicketCosts
                            WHERE duration = 'Single') * trips,
                           (SELECT cost
                            FROM TicketCosts
                            WHERE duration = 'Day')) AS cost
FROM
  (SELECT ct.c_id,
          DATE(bt.start_time) AS date,
          count(*) AS trips
   FROM CustomerTrip ct
   JOIN BusTrip bt ON ct.b_id = bt.b_id
   WHERE c_id = 1
     AND DATE(bt.start_time) = '2023-09-11'
   GROUP BY ct.c_id,
            DATE(bt.start_time)) AS day_summary

    Result:

    c_id date trips cost
    1 2023-09-11 3 500

    fiddle

    Login or Signup to reply.
  2. 0

    We start with JOIN both CustomerTrip & BusTrip, which contain information about customers’ trips and the corresponding bus trips. We use the GROUP BY clause to group the results by customer ID c_id and the date of the bus trip DATE(bt.start_time). This grouping is needed to sum up number of trips a customer took on a particular day.

    We can use the CASE statement to determine the cost based on the count(*) for each group.

    SELECT ct.c_id, DATE(bt.start_time) AS date, 
  CASE
    WHEN count(*) = 1 THEN (SELECT cost FROM TicketCosts WHERE duration = 'Single')
    WHEN count(*) = 2 THEN (SELECT cost FROM TicketCosts WHERE duration = 'Single') * 2
    ELSE (SELECT cost FROM TicketCosts WHERE duration = 'Day')
  END AS cost
FROM CustomerTrip ct
JOIN BusTrip bt ON ct.b_id = bt.b_id
GROUP BY ct.c_id, DATE(bt.start_time) order by c_id;

    Here is the demo.
    enter image description here

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search