Checking Multiple Fields in a Table

  • I have a table that has the race of an individual - the individual can have multiple races checked.

    The DDL for the table is as follows:

    CREATE TABLE RACE(

    MEMBNO CHAR(15) NOT NULL,

    RACE_AI CHAR(1) NULL,

    RACE_AS CHAR(1) NULL,

    RACE_WH CHAR(1) NULL,

    RACE_BL CHAR(1) NULL,

    RACE_NH CHAR(1) NULL,

    RACE_NA CHAR(1) NULL)

    I am trying to get the count of individuals by race. RACE_NA is race unknown. If more than one race is checked (excluding RACE_NA), then the individual should be counted only under 'Multiple_Race' and not under each of the individual races.

    MEMBNO is unique and the count of individuals should add up to the total number of records of the table - there should be no duplication across race categories.

    I am looking for the result to be something like below (if the total number of records in the table is 100):

    Race Individual Count

    RACE_AI 15

    RACE_AS 5

    RACE_WH 25

    RACE_BL 33

    RACE_NH 6

    MULTIPLE_RACE 14

    RACE_NA 2

    Please advise.

    Thanks in advance for your help.

  • Something like this?

    CREATE TABLE RACE(

    MEMBNO CHAR(15) NOT NULL,

    RACE_AI CHAR(1) NULL,

    RACE_AS CHAR(1) NULL,

    RACE_WH CHAR(1) NULL,

    RACE_BL CHAR(1) NULL,

    RACE_NH CHAR(1) NULL,

    RACE_NA CHAR(1) NULL)

    INSERT INTO RACE

    VALUES ('0001', 'Y', NULL, NULL, NULL, NULL, NULL)

    ,('0002', NULL, 'Y', NULL, NULL, NULL, NULL)

    ,('0003', NULL, NULL, 'Y', NULL, NULL, NULL)

    ,('0004', 'Y', NULL, NULL, 'Y', NULL, NULL)

    ,('0005', 'Y', NULL, NULL, NULL, NULL, NULL)

    ,('0006', 'Y', NULL, NULL, NULL, 'Y', NULL)

    ,('0007', NULL, NULL, NULL, NULL, NULL, 'Y')

    ,('0008', NULL, NULL, NULL, NULL, 'Y', NULL);

    WITH CTE AS(

    SELECT MEMBNO, CASE WHEN COUNT( CASE WHEN Value = 'Y' THEN Value END) > 1 THEN 'MULTIPLE_RACE' ELSE MAX( Race) END RACE

    FROM RACE

    CROSS APPLY (VALUES('RACE_AI', RACE_AI)

    ,('RACE_AS', RACE_AS)

    ,('RACE_WH', RACE_WH)

    ,('RACE_BL', RACE_BL)

    ,('RACE_NH', RACE_NH)

    ,('RACE_NA', RACE_NA))up(Race,Value)

    WHERE Value IS NOT NULL

    GROUP BY MEMBNO

    )

    SELECT Race, COUNT(*)

    FROM CTE

    GROUP BY Race

    GO

    DROP TABLE RACE

    Reference: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • some sample data will help please

    please see this post

    http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    can you explain a little more as to why you allow multiple race choice....??

    EDIT...seems that Luis has very kindly done the sample data for you

    trust you have a solution now

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Multiple races are allowed to help identify individuals of many races - for e.g., dad could be one race and mom could be another.

    Thanks

  • Thank you very much Luis.

  • Sjey (11/19/2015)


    Thank you very much Luis.

    You're welcome.

    Now the most important question. Do you understand how the code works? Would you be able to explain it if needed?

    Remember that you'll be the one supporting it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply