CODEWITHSUNDEEP
mysqlsql
Karem
Karem

Reputation: 18103

Grabbing last order date, total orders and total amount with one query?

I first planned to do this with multiple queries and some PHP.

What I would like to do is grabbing last time a specific user has placed an order, how much total orders do the user have, and what are the total amount/cost/count he have paid.

What I have tried is this SQL:

SELECT `orders`.`date_created`, 
SUM(total_count) as total_sum, 
COUNT(id) AS total_orders 
FROM `orders` 
WHERE `user_id` = '96838' 
AND (`status` = 'new' OR `status` = 'delivered') 
ORDER BY `orders`.`date_created` DESC
LIMIT 1

What I expected from the above is:

total_sum = total count/amount of all the orders that the user has.
total_orders = total orders
date_created = grab the last orders date_created, so we can know when the last time was.

When I run the above SQL today, I do receive correct total_sum, and total_orders values, but the date_created is wrong (it picks the first order and not the last?)

And are "LIMIT 1" necessary?

Upvotes: 0

Views: 421

Answers (1)

Aleks G
Aleks G

Reputation: 57316

If I understand what you're after, you don't need to group - just get the max date:

SELECT
    MAX(date_created) as last_order_date, 
    SUM(total_count) as total_sum, 
    COUNT(id) AS total_orders 
FROM
    `orders` 
WHERE
    `user_id` = '96838' 
    AND
    `status` IN ('new', 'delivered')

Upvotes: 1

Related Questions