SQL Query Help Finding Highest Order Value From Each Day

user_15931796 0
Viewed 698

I have two tables, Customer and Order.

In the Order table, I have a column called date_time that stores the date and time of an order. I also have the CustomerID.

I want to get the customers ID for the orders with the highest value from that day.

This is the query to retrieve the order with the highest amount in each day:

SELECT 
    MAX(order_amount) AS "Highest Day Amount", 
    to_char(date_time, 'dd/mm/yyyy') AS "ORDER DATE"
FROM 
    orders
GROUP BY 
    to_char(date_time, 'dd/mm/yyyy');

I need to use to_char because the date_time column contains both the date and time (for example: 19/05/2021 17:50) and if I don't use the to_char because I have more than one order in each day, it will consider the date to be different, because of the time component, and it will list two orders on that day instead of 1 order with the highest total.

And then I want to fetch the customer id from those orders, however I'm not sure how to do that.

oracle-apex-5.1 oracle sql
2 Answers

I want to get the customers ID for the orders with the highest value from that day.

You should not need to_char(), but how you truncate to the date depends on the database. The key idea is that you want to use a window function:

SELECT o.*
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY to_char(date_time, 'YYYY-MM-DD') ORDER BY order_amount DESC) as seqnum
      FROM orders o
     ) o
WHERE seqnum = 1;

This returns the entire row with the highest order amount. You can format the result set how you want.

In Oracle, you can do this with aggregation. I would recommend:

select trunc(date_time), max(order_amount),
       max(customer_id) keep (dense_rank first order by order_amount desc) as customer_id
from orders o
group by trunc(date_time);

The keep syntax is Oracle's (rather verbose) way of implementing a "first" aggregation function.

user_11440350

You only need to add your customers IDcto the select and the group by

SELECT MAX(order_amount) as "Highest Day Amount", to_char(date_time, 'dd/mm/yyyy') AS "ORDER DATE", customerID
 FROM orders GROUP BY to_char(date_time, 'dd/mm/yyyy'), customerID

If you want more details from customers, you will need to join this table to customers table

user_154153300
Related