根据客户类型获取数据组的sql查询，如果没有找到客户类型，则需要添加默认值

I have a table "Customers" with columns CustomerID, MainCountry and CustomerTypeID.

我有一个包含CustomerID、MainCountry和CustomerTypeID列的“客户”表。

I have 5 customer types 1,2,3,4,5 .

我有5个客户类型1 2 3 4 5。

I want to count number of customers of each country according to customer type. I am using the following query:

我想根据客户类型来计算每个国家的客户数量。我正在使用以下查询:

select count(CustomerID) as CustomerCount,MainCountry,CustomerTypeID 
from Customers 
group by CustomerTypeID,MainCountry

But some countries not have any customers, under type 1,2,3,4 or 5.

但有些国家没有任何客户，在1、2、3、4或5号。

So I want to put a default value 0 for if customer type is not exist for that country.

如果客户类型在那个国家不存在，我想给它设一个默认值0。

Currently it is giving data as follows :-

目前它提供的数据如下:-。

CustomerCount   MainCountry CustomerTypeID
5695                    AU  1
525                     AU  2
12268                   AU  3
169                     AU  5
18658                   CA  1
1039                    CA  2
24496                   CA  3
2259                    CA  5
2669                    CO  1
10                      CO  2
463                     CO  3
22                      CO  4
39                      CO  5

As "AU" not have type 4 so I want a default value for it.

因为“AU”没有类型4，所以我需要它的默认值。

3 个解决方案

#1

Select Country.MainCountry, CustomerType.CustomerTypeId, Count(T.CustomerID) As CustomerCount
From   (Select Distinct MainCountry From Customers) As Country
       Cross Join (Select Distinct CustomerTypeId From Customers) As CustomerType
       Left Join Customers T
         On Country.MainCountry = T.MainCountry
         And CustomerType.CustomerTypeId = T.CustomerTypeId
             -- Edit here
             And T.CreatedDate > Convert(DateTime, '1/1/2013')
             -- End Edit
Group By Country.MainCountry, CustomerType.CustomerTypeId
Order By MainCountry, CustomerTypeId

#2

You should JOIN your table with a table with TypeId's. In this case

您应该将您的表与类型id的表连接在一起。在这种情况下

select count(CustomerID) as CustomerCount,TypeTable.MainCountry,TypeTable.TId
from 
Customers 
 RIGHT JOIN (
            select MainCountry,TId from
            (
            select Distinct MainCountry from Customers
            ) as T1,  
            (
               select 1 as Tid
               union all 
               select 2 as Tid
               union all 
               select 3 as Tid
               union all 
               select 4 as Tid
               union all 
               select 5 as Tid
             ) as T2

) as TypeTable on Customers.CustomerTypeID=TypeTable.TId
                  and Customers.MainCountry=TypeTable.MainCountry 

group by TypeTable.TId,TypeTable.MainCountry

#3

Try that:

试一试:

with cuntry as (
Select Distinct MainCountry From Customers
),
CustomerType as (
(Select Distinct CustomerTypeId From Customers
),
map as (
select MainCountry, CustomerTypeId from cuntry,CustomerType 
)

select count(CustomerID) as CustomerCount,a.MainCountry,a.CustomerTypeID
from
map a left join Customers b on a.CustomerCount=b.CustomerCount and a.CustomerTypeID=b.CustomerTypeID

#1

Select Country.MainCountry, CustomerType.CustomerTypeId, Count(T.CustomerID) As CustomerCount
From   (Select Distinct MainCountry From Customers) As Country
       Cross Join (Select Distinct CustomerTypeId From Customers) As CustomerType
       Left Join Customers T
         On Country.MainCountry = T.MainCountry
         And CustomerType.CustomerTypeId = T.CustomerTypeId
             -- Edit here
             And T.CreatedDate > Convert(DateTime, '1/1/2013')
             -- End Edit
Group By Country.MainCountry, CustomerType.CustomerTypeId
Order By MainCountry, CustomerTypeId

#2