How to count up how many times a person appears in a related table in SQL?

Wednesday, December 1st, 2010 at 12:14 am

Hi there,

I have two SQL tables which I'd like to do a query on to combine in a certain way. The first lists customers, named Customers, and has a few columns including user_id, telephone and more including their contact details. The second lists all of the orders, named Orders, that a customer has made and includes the column user_id again, along with a few more including details of the order.

What I'd like to do is use an SQL query to output a table which includes the columns user_id, telephone and num_orders, so taking two columns from the first table and calculating a third column based on the second table. The num_orders column would be a count of how many times the customer appears in the Orders table, ie. how many orders they have made.

If feel like this should be reasonably simple but I can't seem to get my head around it. Any help appreciated!