SQL query with count distinct using multiple columns-CodePudding

Having the following table in SQL Server:

create table [dbo].[stats](
    [customerid] [int] NOT NULL,
    [username] [nvarchar](256) NOT NULL,
    [source] [int] NOT NULL,
    [destination] [int] NOT NULL,
    [date] [smalldatetime] NOT NULL
)

populated with data:

customerid  username    source  destination date
1           user1         1        1        2022-05-06 00:00:00
1           user2         2        1        2022-05-06 00:00:00
1           user21        1        2        2022-05-06 00:00:00

Is there a way to create a query to display counts of how many unique users used each type of source and destination for each customerid?

Using the above sample I'd like to have the following output:

customerid source1 source2 destination1 destination2
1            2        1         2           1

Pseudo query:

select customerid,
    count(distinct(username   source)) where source = 1) as source1,
    count(distinct(username   source)) where source = 2) as source2,
    count(distinct(username   destination)) where destination = 1) as destination1,
    count(distinct(username   destination)) where destination = 2) as destination2,
from stats
group by customerid

CodePudding user response：

You can use case

select 
  customerid,
  count(distinct(case 
                 when source = 1
                 then username
                 End)) as source1,
    count(distinct(case
                 when source = 2
                 Then username
                 End )) as source2,
    count(distinct(case 
                 when destination = 1
                 Then username
                 End)) as destination1,
    count(distinct(case
                 when destination = 2
                 Then username
                 End)) as destination2,
from stats
group by customerid;

CodePudding user response：

select userID, source, destination, count(*)
from stats
group by userID, source, destination

CodePudding user response：

Looks like you just need a conditional count

select customerid, 
    Count(case when source=1 then 1 end) Source1,
    Count(case when source=2 then 1 end) Source2,
    Count(case when destination=1 then 1 end) destination1,
    Count(case when destination=2 then 1 end) destination2
from stats
group by customerid;