Pulling in distinct vlaues for multiple columns-CodePudding

I have the following simple query. I am trying to have this query only pull distinct values for country,cost_Center,org_leader,and org_owner. The goal is to see how many unique instances of these occur as there are instances where there are multiple org owners for some gearing ratio ref. I am not sure if there is some type of distinct formula that can pull in values for multiple vs just one column.

select country,cost_center,org_leader,org_owner,gearing_ratio_ref
from wbr_na.hc_gearing_ratio_na

CodePudding user response：

The keyword DISTINCT will eliminate duplicates so the following query will return each combination present once.

select DISTINCT
   country,cost_center,
   org_leader,
   org_owner,
   gearing_ratio_ref
from 
   wbr_na.hc_gearing_ratio_na

We can then use this query with an alias to count the number of unique combinations as follows:


SELECT COUNT(*) unique_records
FROM
(
select DISTINCT
   country,cost_center,
   org_leader,
   org_owner,
   gearing_ratio_ref
from 
   wbr_na.hc_gearing_ratio_na) X;

which gives the answer you need.

CodePudding user response：

If you only want distinct values for country, cost_center, org_leader, org_owner what do you want the output to be when there are two multiple gearing_ratio_ref for that distinct set. Show sample data and expected results and we can give you the query. If all you want is a distinct list of those column obviously all you have to do is:

select distinct country, cost_center, org_leader, org_owner
from wbr_na.hc_gearing_ratio_na

if you want to know how many different gearing_ratio_ref for each then:

select country, cost_center, org_leader, org_owner, count(*)
from wbr_na.hc_gearing_ratio_na
group by gearing_ratio_ref

CodePudding user response：

Yes, you can apply Distinct on multiple columns.

SELECT DISTINCT country,cost_center,org_leader,org_owner
FROM wbr_na.hc_gearing_ratio_na