Count people who have dual citizenship

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  October 15, 2018 at 4:46 am

  #379567

  Hi,
  I’m trying to count the number of people who are coming from different countries in sql server. But some people have dual citizen ship too.
  How to write query considering dual citizenship too and count people in both countries.

  Example:

  ID	CITIZENSHIP	DUAL CITIZENSHIP
  00000S123	US	ITALY
  00000BH01	UK
  00000VA03	US
  00000SR01	UK	US

  Ideally there are only two students with US citizen ship but it should be 3 as a student is also having dual citizenship for US.
  Please note I don’t want to hard code in where clause WHERE  DUAL_CITIZENSHIP=’US’

  Thanks,
  Sindhu

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 15, 2018 at 4:48 am

  #2009560

  What have you tried so far? If you don't want to put it in the WHERE, are you saying your returning every row and the counting only the relevant rows in the SELECT?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  October 15, 2018 at 4:57 am

  #2009562

  I would like to get some like this
  Country Number of people 
  US              3
  UK             2
  ITALY        1

  I tried to get the count only from citizenship, would like to know from you, how to solve this using dual citizenship.

  Regards,
  Sindhu

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 15, 2018 at 5:01 am

  #2009563

  sindhupavani123 - Monday, October 15, 2018 4:57 AM

  I would like to get some like this
  Country Number of people 
  US              3
  UK             2
  ITALY        1

  I tried to get the count only from citizenship, would like to know from you, how to solve this using dual citizenship.

  Regards,
  Sindhu

  Can you post your attempted code to get your intended result, please? Also, do you have a (separate) table of all the countries?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  October 15, 2018 at 10:07 am

  #2009648

  
;WITH cte_test_data AS (
    SELECT * FROM ( VALUES
        ('00000S123', 'US', 'ITALY'),
        ('00000BH01', 'UK', ''),
        ('00000VA03', 'US', ''),
        ('00000SR01', 'UK', 'US') )
    AS data(ID, CITIZENSHIP, [DUAL CITIZENSHIP])
)
SELECT ca.CITIZENSHIP AS Country, COUNT(*) AS [Number of people]
FROM cte_test_data
CROSS APPLY (
    SELECT CITIZENSHIP
    WHERE CITIZENSHIP > ''
    UNION ALL
    SELECT [DUAL CITIZENSHIP]
    WHERE [DUAL CITIZENSHIP] > ''
) AS ca
GROUP BY ca.CITIZENSHIP
ORDER BY ca.CITIZENSHIP

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  October 15, 2018 at 4:50 pm

  #2009770

  sindhupavani123 - Monday, October 15, 2018 4:46 AM

  I see that you feel you are exempt from the rules of the forum and didn’t bother to post any DDL. I also see if I’m reading your narrative correctly, that you do know what normalization is. Please get a book on basic RDBMS and look up first normal form (1NF). You also don’t seem to know that there is no such thing as a generic id in RDBMS, nor do you seem to know the ISO country codes.

  CREATE TABLE Personnel
(emp_id CHAR(9) NOT NULL,
citizenship CHAR(3) NOT NULL,
PRIMARY KEY (emp_id, citizenship),
..);

  CREATE VIEW Multiple_Citizenships
AS
SELECT emp_id, COUNT(*) AS citizenship_cnt
FROM Personnel
GROUP BY emp_id
HAVING COUNT(*) > 1;


  Please post DDL and follow ANSI/ISO standards when asking for help.