select number of times result of one query occurs in another table
let's say I have two tables:
开发者_Go百科USERS:
User_Id
Hansen
Nilsen
Jensen
Bob
and
ORDERS:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
I want to do a query to show how many times a customer ordered, so it'd look like:
Customer Orders
Hansen 3
Nilsen 2
Jensen 1
Bob 0
I know I have to use the COUNT function, but I am at a loss as to how to finish this query off...
SELECT Customer, COUNT(*) AS Orders
FROM ORDERS RIGHT OUTER JOIN
USERS ON USERS.User_Id = ORDERS.Customer
GROUP BY USERS.User_Id
ORDER BY COUNT(*) DESC
精彩评论