Clever Geek Handbook

SQL. Aggregate column based on various criteria.

Let's say I have an Orders table 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 the table like so,

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

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

Here's what I did and it didn't get the result I want.

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


source to share


3 answers 


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

+2


source


First, create a table containing each customer and the number of orders they have in each country where each row is country, customer_id, number_of_orders

Now you can count how often it number_of_orders

is 2 or more than 2 by grouping the derived table



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
+1


source 


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


source






More articles:


All Articles