November 18, 2015 at 8:49 am
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.
November 18, 2015 at 9:06 am
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/
November 18, 2015 at 9:07 am
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
November 19, 2015 at 11:36 am
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
November 19, 2015 at 11:48 am
Thank you very much Luis.
November 19, 2015 at 12:07 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply