Best solution for SQL without looping

Question

I'm relatively new to SQL, and am trying to find the best way to attack this problem.

I am trying to take data from 2 tables and start merging them together to perform analysis on it, but I don't know the best way to go about this without looping or many nested subqueries.

What I've done so far: I have 2 tables. Table1 has user information and Table2 has information on orders(prices and dates, as well as user)

What I need to do: I want to have a single row for each user that has a summary of information about all of their orders. I'm looking to find the sum of prices of all orders by each user, the max price paid by that user, and the number of orders. I'm not sure how to best manipulate my data in SQL.

Currently, my code looks as follows:

Select alias1.*, Table2.order_id, Table2.price, Table2.order_date
From (Select * from Table1 where country='United States') as alias1
LEFT JOIN Table2
on alias1.user_id = Table2.user_id

This filters out the datatypes by country, and then joins it with users, creating a record of each order including the user information. I don't know if this is a helpful step, but this is part of my first attempt playing around with the data. I was thinking of looping over this, but I know that is against the spirit of SQL

Edit: Here is an example of what I have and what I want:

Table 1(user info):

user_id    user_country
1          United States
2          United Kingdom
(etc)

Table 2(order info):

order_id    price    user_id
100         5.00     1
101         3.50     2
102         2.50     1
103         1.00     1
104         8.00     2

What I would like output:

user_id    user_country      total_price   max_price    number_of_orders
1          United States     8.50          5.00         3
2          United Kingdom    11.50         8.00         2

Answer 1

Here's one way to do this:

SELECT    alias1.user_id, 
          MAX(alias1.user_name) As user_name,
          SUM(Table2.price)     As UsersTotalPrice,
          MAX(Table2.price)     As UsersHighestPrice
FROM      Table1                As alias1
LEFT JOIN Table2    ON alias1.user_id = Table2.user_id
WHERE     country   = 'United States'
GROUP BY  user_id

If you can give us the actual table definitions, then we can show you some actual working queries.