MartinZeltin
6 months ago
495
1
General

Can't get the last order sum for each day when using group by and joins

Posted 6 months ago by MartinZeltin

I have two tables orders and order_items. I need to group the results by days.

But I also need to get for each day:

  • the sum of order_items.energy_used for all orders created that day
  • the sum of orders.order_sum for all orders created that day
  • the last created_at and order_sum that correspond to the latest order created on that day

I am able to get everything except the last sum of order_sum for each day.

Any help would be much appreciated

Here is my orders table

+----+-----------+---------+---------------------+
| id | order_sum | user_id | created_at          |
+----+-----------+---------+---------------------+
| 1  | 25.13     | 7       | 2020-01-25 09:13:00 |
| 2  | 10.00     | 7       | 2020-01-25 15:23:00 |
| 3  | 14.00     | 5       | 2020-01-26 10:14:00 |
| 4  | 50.00     | 5       | 2020-01-26 19:14:00 |
| 5  | 35.00     | 1       | 2020-01-27 11:13:00 |
+----+-----------+---------+---------------------+

And here is my order_items table

+----+----------+-------------+---------------------+
| id | order_id | energy_used | created_at          |
+----+----------+-------------+---------------------+
| 1  | 1        | 65          | 2020-01-25 09:13:00 |
| 2  | 1        | 12          | 2020-01-25 09:13:00 |
| 3  | 1        | 12          | 2020-01-25 09:13:00 |
| 4  | 2        | 70          | 2020-01-25 15:23:00 |
| 5  | 2        | 5           | 2020-01-25 15:23:00 |
| 6  | 3        | 0           | 2020-01-26 10:14:00 |
| 7  | 4        | 1           | 2020-01-26 19:14:00 |
| 8  | 5        | 5           | 2020-01-27 11:13:00 |
+----+----------+-------------+---------------------+

And this is the desired result that I am trying to achieve

+---------------+-----------------+-------------------+---------------------+----------------+
| date_of_month | total_order_sum | total_energy_used | last_order_date     | last_order_sum |
+---------------+-----------------+-------------------+---------------------+----------------+
| 2020-01-25    | 35.13           | 164               | 2020-01-25 15:23:00 | 10.00          |
| 2020-01-26    | 64.00           | 1                 | 2020-01-26 19:14:00 | 50.00          |
| 2020-01-27    | 35.00           | 5                 | 2020-01-27 11:13:00 | 35.00          |
+---------------+-----------------+-------------------+---------------------+----------------+

With this query I can get everything except the last order_sum of each day

select 
    date(o.created_at) date_of_month,
    sum(i.total_energy_used),
    max(o.created_at),
    sum(order_sum) as total_order_sum
from orders o
inner join (
    select order_id, sum(energy_used) total_energy_used
    from order_items i
    group by order_id
) i on o.id = i.order_id
group by date(o.created_at)

Please sign in or create an account to participate in this conversation.