开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜