How to make a selection from the database using functions and remove empty results in these functions?

Question

SQL

Hello!

There is a table of users with fields
id, name, phone, email, created

And also another table where the orders of these users are stored, with the following fields
id, summa, user_id, created
The created field is stored in this format Ymd H:i:s

And you need to get the following data in one request:
1. User name
2. User phone
3. The sum of all the user's orders
4. The average bill of the user
5. When the user last placed an order

In this case, it is necessary to remove from the query result when there are no records in the orders at all.

I implemented it like this:

SELECT users.name, users.phone, (SELECT MAX(orders.subtotal) FROM orders WHERE orders.user_id=1) as max_order, (SELECT ROUND(AVG(orders.subtotal)) FROM orders WHERE orders.user_id=1) as average_check, (SELECT MAX(orders.created) FROM orders WHERE orders.user_id=1) as last_day_of_order FROM users WHERE users.id=1

But this option does not take into account that there may be no orders at all and it would be necessary to somehow show an empty result.

I would be very grateful for any hint.

Answer 1

select u.name, u.phone, sum(o.summa) total, avg(o.summa) average, max(o.created) last_date
from orders o, `users` u
where u.id=o.user_id
group by u.id
having total > 0

I hope "Field created in format Ymd H:i:s" is not a string, but a DATETIME.