SQL -通过不同的标准聚合同一列。

Say I have a table Orders that looks like this,

假设我有一个像这样的表格，

|country| customer_id | order_id |
| CA    | 5           |     3    |
| CA    | 5           |     4    |
| CA    | 6           |     5    |
| CA    | 6           |     6    |
| US    | 2           |     7    |
| US    | 7           |     8    |
| US    | 7           |     9    |
| US    | 7           |    10    |
| US    | 2           |    11    |

and I want to write a query to populate a table as so,

我想写一个查询来填充一个表，

| country | customers_w_2_orders | customers_w_2_plus_orders |
| CA      | 2                    | 0                         |
| US      | 1                    | 1                         |

where it aggregates number of customers with 2 orders and number of customers with 3 orders by country.

其中，按国家划分为2个订单和3个订单的客户数量。

Here's what I did and it did not give the result I want..

这是我所做的，它并没有给出我想要的结果。

SELECT country, count(*) as cnt1, count(*) as cnt2 
FROM Orders 
GROUP BY country 
HAVING cnt1=2 AND cnt2>2;

3 个解决方案

#1

declare @orders table (country char(2), customer_id int, order_id int);
insert into @orders values
('CA', 5, 3),
('CA', 5, 4),
('CA', 6, 5),
('CA', 6, 6),
('US', 2, 7),
('US', 7, 8),
('US', 7, 9),
('US', 7, 10),
('US', 2, 11);

select country,
       sum(case when num_orders <= 2 then 1 else 0 end) as cust_w_2_orders,
       sum(case when num_orders > 2 then 1 else 0 end) as cust_2_plus_orders
from (
      select country, customer_id, count(*) num_orders
      from   @orders
      group by country, customer_id
     ) x
group by country;
GO

country | cust_w_2_orders | cust_2_plus_orders
:------ | --------------: | -----------------:
CA      |               2 |                  0
US      |               1 |                  1

dbfiddle here

dbfiddle这里

#2

First construct a table that contains every customer and the # of orders they have per country where each row is country, customer_id, number_of_orders

首先构建一个表，其中包含每个客户和每个国家的订单号，其中每行是国家、customer_id、number_of_orders

Now you can count how often number_of_orders is 2 or greater than 2 by grouping on the derived table

现在，通过对派生表进行分组，您可以计算number_of_orders大于或等于2的频率

select country, sum(num_orders = 2), sum(num_orders > 2)
from (
    select country, customer_id, count(*) as num_orders
    from Orders
    group by country, customer_id
) t group by country

#3

SELECT country,
       (select count(distinct(customer_id)) from Orders o where o.country = Orders.country and (select count(*) from Orders o2 where o2.country = orders.country and o2.customer_id = o.customer_id) = 2) as customers_w_2_orders,
       (select count(distinct(customer_id)) from Orders o where o.country = Orders.country and (select count(*) from Orders o2 where o2.country = orders.country and o2.customer_id = o.customer_id) > 2) as customers_w_2_plus_orders
  FROM Orders 
 GROUP BY country;

#1

declare @orders table (country char(2), customer_id int, order_id int);
insert into @orders values
('CA', 5, 3),
('CA', 5, 4),
('CA', 6, 5),
('CA', 6, 6),
('US', 2, 7),
('US', 7, 8),
('US', 7, 9),
('US', 7, 10),
('US', 2, 11);

select country,
       sum(case when num_orders <= 2 then 1 else 0 end) as cust_w_2_orders,
       sum(case when num_orders > 2 then 1 else 0 end) as cust_2_plus_orders
from (
      select country, customer_id, count(*) num_orders
      from   @orders
      group by country, customer_id
     ) x
group by country;
GO

country | cust_w_2_orders | cust_2_plus_orders
:------ | --------------: | -----------------:
CA      |               2 |                  0
US      |               1 |                  1