oracle 如何编写sql以在一个聚合上执行另一个聚合

iqjalb3h  于 2022-12-11  发布在  Oracle
关注(0)|答案(1)|浏览(131)

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 ?

ercv8c1e

ercv8c1e1#

这将得到您想要的结果。您按客户分组以了解他们下了多少订单。然后也对该子集进行分组。

select orders, count(*) customers
from (
   select cust_id, count(*) orders
   from tbl
   group by cust_id
   )
group by orders

相关问题