0

I have the following but I am not getting the correct totals because GROUP BY does not add the group user id's total revenue. 

SELECT
  users.id,
  Count(orders.id) AS total_orders,
  SUM(item_orders.item_price * item_orders.quantity) AS total_rev
FROM
  orders 
LEFT JOIN
  users ON orders.user_id = users.id 
LEFT JOIN
  item_orders ON item_orders.order_id = orders.id
WHERE 
  orders.date >= '2015-10-06 00:00:00' AND orders.date <= '2016-03-23 23:59:59'
GROUP BY
  users.id  -- ignores duplicate ids, doesn't sum total_rev for those
ORDER BY
  total_rev DESC 
LIMIT 0,25

I would like to use WITH ROLLUP to solve this but when I use WITH ROLLUP I can not use ORDER BY and I can't order with GROUP BY with an alias which contains my total per user id.

SELECT
  users.id,
  Count(orders.id) AS total_orders,
  SUM(item_orders.item_price * item_orders.quantity) AS total_rev
FROM
  orders 
LEFT JOIN
  users ON orders.user_id = users.id 
LEFT JOIN
  item_orders ON item_orders.order_id = orders.id
WHERE 
  orders.date >= '2015-10-06 00:00:00' AND orders.date <= '2016-03-23 23:59:59'
GROUP BY
  users.id, total_rev DESC WITH ROLLUP -- does not work because total_rev is an alias!
LIMIT 0,25

Any suggestions on how I can get this to work? Does it make sense I do this in the DB? I am trying to save resources by letting the DB presort so I can do the paging in my application.

EDIT: As xjstratedgebx mentioned in the comments below the first query works fine and there is no reason for using WITH ROLLUP. The first query can also be shortened like so:

SELECT
  orders.user_id,
  Count(orders.id) AS total_orders,
  SUM(item_orders.item_price * item_orders.quantity) AS total_rev
FROM
  orders 
LEFT JOIN
  item_orders ON item_orders.order_id = orders.id
WHERE 
  orders.date >= '2015-10-06 00:00:00' AND orders.date <= '2016-03-23 23:59:59'
GROUP BY
  orders.user_id
ORDER BY
  total_rev DESC 
LIMIT 0,25
sschueller
  • 533
  • 1
  • 6
  • 18
  • 1
    I'm curious about the comment in the first block that reads `-- ignores duplicate ids, doesn't sum total_rev for those`. When doing a SUM with a GROUP BY, won't it SUM all collapsed/hidden rows? Or is that what you're going for: a single row per user, but the total of all their rows including the collapsed rows, and then sorted by that total? Would you consider using a subquery, or would that violate the request to use 1 query in your mind? – stratedge Mar 20 '16 at 17:31
  • It doesn't appear to SUM all collapsed rows. It may have to do with that I am collapsing twice, once the items of an order and then those orders into a user. I would consider a sub query still being "one" query in this case. I am looking for the total order amount per user. – sschueller Mar 20 '16 at 18:04
  • 1
    Hmmm, I just recreated a version of your query, conceptually at least, on one of my databases, and it appears to do the job - for each row for each user, it multiplies the two columns, and then in collapsing the rows sums all the individually derived values. The ORDER BY on that computed column works correctly, and the LIMIT gives me the top 25 back. So weird. – stratedge Mar 20 '16 at 18:30
  • 1
    On a slightly unrelated note, you may be able to dispense with the first LEFT JOIN if you don't need any actual data from that table - the orders table has the user's id which you can GROUP BY. And depending on whether or not orders can potentially have missing users and/or missing items, you may get a significant performance boost using just simple JOINS. That all being said, I think your general strategy is sound - doing the sort in the database is a good idea. If you grab n+1 rows, you can know if there should be a "more" button. If a 26th does come back, just drop it from your results. – stratedge Mar 20 '16 at 18:38
  • You are correct. It does work, it turns out my manual query to calculate the total for one user ignored the date range :| . Also thank you for the `LEFT JOIN` tip. I do not need the user column which does save me processing power. – sschueller Mar 20 '16 at 20:28
  • 1
    I'm sorry your other query wasn't working as you thought, but man am I glad that we weren't both going crazy and were right that was in fact a valid approach! – stratedge Mar 20 '16 at 20:31

0 Answers0