I've run into a weird issue with WITH ROLLUP in MySQL that I am having trouble understanding. I have a database of orders for a restaurant, and it is giving me different totals at the bottom based on how the query is put together.
The below query calculated correctly, and uses SUM(m.price*od.qty).
SELECT m.item_name, m.price, SUM(od.qty), COUNT(*), SUM(m.price*od.qty)
FROM order_main AS om, order_detail AS od, menu as m
WHERE od.menuid=m.menuid AND om.orderid=od.orderid AND om.order_date='2012-11-16' AND m.menuid<10
GROUP BY m.menuid WITH ROLLUP;
+-------------------+-------+-------------+----------+---------------------+
| item_name | price | SUM(od.qty) | COUNT(*) | SUM(m.price*od.qty) |
+-------------------+-------+-------------+----------+---------------------+
| Cheese Sticks | 8.74 | 31 | 11 | 270.94 |
| Pepper Pasta | 1.63 | 55 | 18 | 89.65 |
| Sambuca Puree | 2.84 | 68 | 22 | 193.12 |
| Beef Tenderloin | 2.52 | 48 | 16 | 120.96 |
| Pork Chops | 5.37 | 53 | 18 | 284.61 |
| Sole Nole | 2.13 | 65 | 18 | 138.45 |
| Nescafe Espresso | 9.96 | 56 | 21 | 557.76 |
| Lettuce Wraps | 8.35 | 57 | 21 | 475.95 |
| Bread with Butter | 9.36 | 55 | 19 | 514.80 |
| WITH ROLLUP | ---- | 488 | 164 | 2646.24 |
+-------------------+-------+-------------+----------+---------------------+
The below query calculates incorrectly for the rollup using m.price*SUM(od.qty). But everything else is right in the table.
SELECT m.item_name, m.price, SUM(od.qty), COUNT(*), m.price*SUM(od.qty)
FROM order_main AS om, order_detail AS od, menu as m
WHERE od.menuid=m.menuid AND om.orderid=od.orderid AND om.order_date='2012-11-16' AND m.menuid<10
GROUP BY m.menuid WITH ROLLUP;
+-------------------+-------+-------------+----------+---------------------+
| item_name | price | SUM(od.qty) | COUNT(*) | m.price*SUM(od.qty) |
+-------------------+-------+-------------+----------+---------------------+
| Cheese Sticks | 8.74 | 31 | 11 | 270.94 |
| Pepper Pasta | 1.63 | 55 | 18 | 89.65 |
| Sambuca Puree | 2.84 | 68 | 22 | 193.12 |
| Beef Tenderloin | 2.52 | 48 | 16 | 120.96 |
| Pork Chops | 5.37 | 53 | 18 | 284.61 |
| Sole Nole | 2.13 | 65 | 18 | 138.45 |
| Nescafe Espresso | 9.96 | 56 | 21 | 557.76 |
| Lettuce Wraps | 8.35 | 57 | 21 | 475.95 |
| Bread with Butter | 9.36 | 55 | 19 | 514.80 |
| WITH ROLLUP | ---- | 488 | 164 | 4567.68 |
+-------------------+-------+-------------+----------+---------------------+
I cannot find anything on why the WITH ROLLUP would calculate differently, especially since the price of each item is static.
You are using one of MySQL's most tricky features: including fields that are neither in the group by nor aggregated somehow. In your case that is m.price and MySQL will use the first value it encounters. This may differ between executions for any reason. In newer versions this has been disabled by default, since it can lead to exactly this kind of unexpected outcome.
So your second formula, m.price * SUM(od.qty) essentially means: "For each group (menu item) take the first value you find for m.price and multiply that with the sum of the quantity in that group".
This works well for the items because each only has a single price, but for the rollup you get a random price out of the whole data set.
Correct the group by to say "m.menuid, m.price" or use an aggregation like AVG().