Sample data
WITH
tbl AS
(
Select 'UK' "CUST_GRP", '00001' "CUST_ID", 'John' "CUST_NAME", '001' "ORDER_ID", 'Beer' "GOODS" From Dual Union All
Select 'ITA' "CUST_GRP", '00002' "CUST_ID", 'Jack' "CUST_NAME", '002' "ORDER_ID", 'Coca Cola' "GOODS" From Dual Union All
Select 'ITA' "CUST_GRP", '00002' "CUST_ID", 'Jack' "CUST_NAME", '003' "ORDER_ID", 'Fanta' "GOODS" From Dual Union All
Select 'ITA' "CUST_GRP", '00003' "CUST_ID", 'Lisa' "CUST_NAME", '004' "ORDER_ID", 'Beer' "GOODS" From Dual Union All
Select 'ITA' "CUST_GRP", '00003' "CUST_ID", 'Lisa' "CUST_NAME", '005' "ORDER_ID", 'Coffee' "GOODS" From Dual Union All
Select 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '006' "ORDER_ID", 'Wine' "GOODS" From Dual Union All
Select 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '007' "ORDER_ID", 'Juice' "GOODS" From Dual Union All
Select 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '008' "ORDER_ID", 'Beer' "GOODS" From Dual Union All
Select 'FRA' "CUST_GRP", '00005' "CUST_ID", 'Mary' "CUST_NAME", '009' "ORDER_ID", 'Wine' "GOODS" From Dual Union All
Select 'FRA' "CUST_GRP", '00005' "CUST_ID", 'Mary' "CUST_NAME", '020' "ORDER_ID", 'Else' "GOODS" From Dual
)
I need to know the number of customers that have done the same number of orders.
Expected result should be either
| TEXT |
| ------------ |
| the amount of customers who ordered 1 times is 1 |
| the amount of customers who ordered 2 times is 3 |
| the amount of customers who ordered 3 times is 1 |
OR
| Orders | Customers |
| ------------ | ------------ |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
Anyone can tell me how to write this SQL ?
1条答案
按热度按时间ercv8c1e1#
这将得到您想要的结果。您按客户分组以了解他们下了多少订单。然后也对该子集进行分组。