SQL Code working but not giving the right numbers

Question

SELECT 
  bikers.id AS 'Biker ID', 
  bikers.first_name AS 'Biker Name', 
  SUM(offline_bookings.total_price) AS 'Offline Revenue', 
  SUM(offline_bookings.main_service_count) AS 'Offline Cars' , 
  SUM(offline_bookings.extra_service_count) AS 'Extra Service Count',
  COUNT(bookings.id) AS 'Online Cars', SUM(bookings.paid) AS 'Online Revenue'
FROM bikers
JOIN offline_bookings ON bikers.id = offline_bookings.biker_id
JOIN bookings ON bookings.biker_id = bikers.id
WHERE offline_bookings.`date` = '2022-10-10'
  AND bookings.booking_date = '2022-10-10' 
  AND offline_bookings.`status` = TRUE
GROUP BY bikers.id , bikers.first_name
ORDER BY bikers.id , bikers.first_name;

You are cross joining bookings and offline_bookings. Are they related by some booking ID that you forgot in the join? Or are they only losely related by belonging to the same biker? — Thorsten Kettner, Oct 11 '22 at 16:02
With that inner join, you are returning bikers found in both bookings tables. You could try `left join` to both booking tables, but depending on your data and what you're trying to do, you may want to consider a `UNION` or `UNION ALL` so that you can write cleaner conditions. — Isolated, Oct 11 '22 at 16:25
@ThorstenKettner, they only related by belonging to the same biker — Mohammad Al Mashwakhi, Oct 12 '22 at 08:09
@Isolated I don't want to combine rows in my table, I want to combine columns what is the use of union in my case ? — Mohammad Al Mashwakhi, Oct 12 '22 at 08:18
The only relation between online booking table and offline booking table is the biker ID — Mohammad Al Mashwakhi, Oct 12 '22 at 08:36

score 0 · Accepted Answer · answered Oct 12 '22 at 10:18

In the request comments you say that bookings and offline_bookings are only related by belonging to the same biker. But you are joining them on the biker ID. This means for a biker with 4 bookings and 5 offline_bookings you generate 4 x 5 = 20 rows. If you then aggregate and build sums or counts you get every booking five-fold and every offline_booking four-fold.

You don't want to join booking rows with offline_booking rows. You want to join totals instead. So, aggregate your data before joining.

SELECT
  b.id AS biker_id, 
  b.first_name AS biker_name, 
  obk.offline_revenue,
  obk.offline_cars,
  obk.extra_service_count,
  bk.online_cars,
  bk.online_revenue
FROM bikers b
LEFT OUTER JOIN 
(
  SELECT
    biker_id,
    SUM(total_price) AS offline_revenue, 
    SUM(main_service_count) AS offline_cars, 
    SUM(extra_service_count) AS extra_service_count
  FROM offline_bookings
  WHERE `date` = DATE '2022-10-10'
  AND status = TRUE
  GROUP BY biker_id
) obk ON ON obk.biker_id = b.id
LEFT OUTER JOIN
(
  SELECT
    biker_id,
    COUNT(*) AS online_cars,
    SUM(paid) AS online_revenue
  FROM bookings
  WHERE `date` = DATE '2022-10-10'
  GROUP BY biker_id
) bk ON ON bk.biker_id = b.id
ORDER BY b.id;

I've made this outer joins for the case a biker lacks a booking or offline_booking that day. If you only want biker that have both, just make these inner joins again.

I've also removed the invalid single quotes for your column aliases. Single quotes are for string literals. Use standard SQL double quotes or MySQL's propriatary backticks for names instead. Or use names that need no quotes, like I did. If you want column headers with spaces in your app or website, you can care care about that there where it belongs.

SQL Code working but not giving the right numbers

1 Answers1