How To Write A Sql Code To Count Multiple Value In A Row

I have a data like below Id | Country ----+---------------------------- 1 | India 2 | India|United kingdom|Chile 3 | Brazil 4 | Mexico|Canada 5 | China|Hong kong|Ja

Solution 1:

You will have to use case statement in your join. Here is the example

CREATE TABLE Country (
    id INT PRIMARY KEY,
    countryName VARCHAR(255) NOT NULL
);

INSERT INTO Country(id, countryName)
VALUES(1,'India'),
    (2,'India|United kingdom|Chile'),
    (3,'Brazil'),
    (4,'Mexico|Canada');


CREATE TABLE Region (
    countryName VARCHAR(255) NOT NULL,
    regionName VARCHAR(255) NOT NULL,
);


INSERT INTO Region(countryName, regionName)
VALUES('India','Apac'),('United kingdom','Emea'),('Chile','Latam'),('Canada','Canada'),('Mexico','Latam'),('China','Apac'),
('Hong kong','Apac'),('Japan','Apac'),('Brazil','Lathem')

Select C.Id, 
CASE WHEN CHARINDEX('|',C.CountryName) > 0 
     THEN 'Multiple Region' 
     ELSE R.RegionName 
     END as Region 
from Country [C]
LEFT JOIN Region[R] ON [C].countryName = [R].countryName

Key here is left join and case statement where you will find | in column value then you return as multiple region.

Solution 2:

As you don't need to split the values, you could get away with something like this:

select d.id, coalesce(r.region, 'Multiple regions') as region
from data_table d
  left join regions r on r.country = d.country;

The rows that contain multiple entries won't match during the join (that's why an outer join is needed).

The drawback is, that you can't distinguish between a missing country and an entry with multiple countries in the result though.

If you need to distinguish between missing countries and multiple countries, you can do that in a CASE expression:

select d.id, 
       d.country,
       case 
         when d.country like '%|%' then 'Multiple regions'
         else r.region 
       end as region
from data_table d
  left join region r on r.country = d.country;

Solution 3:

It's a combination of Like and Join , something like this

SELECT cnt.id,
CASE WHEN cnt.country like '%|%' then 'Multiple region' 
 else map.region
 END region
from 
(
Select  1 id,   'India' as country union all
Select 2,     'India|United kingdom|Chile' union all
Select 3,   'Brazil' union all
Select 4,  'Mexico|Canada' 
 ) cnt
Left Join  
(
Select 'India'  as country ,          'Apac' as region union all
Select 'United kingdom',   'Emea' union all
Select 'Chile',            'Latam' union all
Select 'Canada',           'Canada'  union all
Select 'Mexico',           'Latam' union all
Select  'China',              'Apac' union all
Select 'Hong kong',     'Apac'  union all
Select 'Japan' ,            'Apac'
  ) map on cnt.country =map.country






'

Solution 4:

You can use a JOIN and aggregation:

select d.id, d.country,
       (case when min(r.region) <> max(r.region)
             then 'Multiple regions'
             else min(r.region) 
        end) as region
from data_table d left join
     region r
     on r.country like '%|' || d.country || '|%'
group by d.id, d.country;

Note that this uses the standard string concatenation operator. Your database might have a different method.

As discussed in the comments, you should fix the data model! Storing multiple values in a string column is a very bad idea in relational databases.