CODEWITHSUNDEEP
sqlsql-servert-sqlgoogle-bigquery
BrownBatman
BrownBatman

Reputation: 199

Counting the count of distinct values from two columns in sql

I have a table in data base in which there are corresponding values for the primary key. I want to count the distinct values from two columns.

I already know one method of using union all and then applying groupby on that resultant table.

Select Id,Brand1
into #Temp
from data
union all
Select Id,Brand2
from data

Select ID,Count(Distinct Brand1)
from #Temp
group by ID

Same thing we can do in big query also using temp table only.

Sample Table 

ID  Brand1  Brand2
1    A       B
1    B       C
2    D       A
2    A       D

Resultant Table

ID  Distinct_Count_Brand
1    3
2    2

As you can see in this column Distinct_count_Brand It is counting the unique count of Brand from two columns Brand1 and Brand2.

I already know one way (Basically unpivoting) but want to know if there is some other way around to count unique values from two columns.

Upvotes: 4

Views: 15510

Answers (3)

noam sondak
noam sondak

Reputation: 137

I just concatenated the two columns like this:

SELECT
  date,
  COUNT( DISTINCT( CONCAT( storeId, '---', userId) ) ) as visits
FROM
  main.reports
GROUP BY
  date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

In SQL Server, I woud use:

Select b.id, count(distinct b.brand)
from data d cross apply
     (values (id, brand1), (id, brand2)) b(id, brand)
group by b.id;

Here is a db<>fiddle.

In BigQuery, the equivalent would be expressed as:

select t.id, count(distinct brand)
from t cross join
     unnest(array[brand1, brand2]) brand
group by t.id;

Here is a BQ query that demonstrates that this works:

with t as (
      select 1 as id, 'A' as brand1, 'B' as brand2 union all
      select 1, 'B', 'C' union all
      select 2, 'D', 'A' union all
      select 2, 'A', 'D'
     )
select t.id, count(distinct brand)
from t cross join
     unnest(array[brand1, brand2]) brand
group by t.id;

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

I don't know BigQuery's quirks, but perhaps you can just inline the union query:

SELECT ID, COUNT(DISTINCT Brand)
FROM
(
    SELECT ID, Brand1 AS Brand FROM data
    UNION ALL
    SELECT ID, Brand2 FROM data
) t
GROUP BY ID;

Upvotes: 5

Related Questions