Reputation: 1473
I have three tables w some records below. I was expecting to return on two distinct Keys with a 'BERRY' in Group field. However, for example the KEYs = 179526189 shows BERRY and COCA instead of just BERRY.
Table A
AGE AGE_IND KEYS
34 (null) 179526189
6 N 179526390
Table B
ID STATUS FK_KEY
16478 (null) 179526189
16478 (null) 179526390
16479 (null) 179526189
16479 (null) 179526390
Table C
FK_ID PRO_CODE DEL_DT
16478 Drug 6/10/2014 12:00:00 AM
16479 Drug (null)
SELECT KEYS,
CASE WHEN (AGE < 18 AND AGE_IND = 'Y') AND (B.STATUS != 'S' or B.STATUS is null) THEN 'APPLE'
WHEN C.PRO_CODE = 'Drug' THEN 'BERRY'
WHEN AGE >= 18 THEN 'COCA'
ELSE 'APPLE' END as Groups,
COUNT(DISTINCT KEYS) AS CT
FROM #tableA A
LEFT OUTER JOIN #tableB B
ON (A.KEYS = B.FK_KEY AND (B.STATUS <>'S' or B.STATUS IS NULL))
LEFT OUTER JOIN #tableC C
ON (B.ID = C.FK_ID AND C.DEL_DT IS NULL)
GROUP BY KEYS,
CASE WHEN (AGE < 18 AND AGE_IND = 'Y') AND (B.STATUS != 'S' or B.STATUS is null) THEN 'APPLE'
WHEN C.PRO_CODE = 'Drug' THEN 'BERRY'
WHEN AGE >= 18 THEN 'COCA'
ELSE 'APPLE' END
running the above query, yields
KEYS Groups CT
179526189 BERRY 1
179526189 COCA 1
179526390 APPLE 1
179526390 BERRY 1
Would you please tell me what is wrong with my CASE expression? so that my expected result yields:
KEYS Groups CT
179526189 BERRY 1
179526390 BERRY 1
Upvotes: 1
Views: 111
Reputation: 1269563
You are grouping by GROUPS
so you get each one. Choose one! It is not clear how you are choosing 'BERRY'
, but if you want one row per KEYS
, then the idea is to only include that in the GROUP BY
:
SELECT KEYS,
MAX(CASE WHEN (AGE < 18 AND AGE_IND = 'Y') AND (B.STATUS != 'S' or B.STATUS is null) THEN 'APPLE'
WHEN C.PRO_CODE = 'FMU' THEN 'BERRY'
WHEN AGE >= 18 THEN 'COCA'
ELSE 'APPLE'
END) as Groups,
COUNT(DISTINCT KEYS) AS CT
FROM #tableA A LEFT OUTER JOIN
#tableB B
ON A.KEYS = B.FK_KEY AND (B.STATUS <>'S' or B.STATUS IS NULL) LEFT OUTER JOIN
#tableC C
ON B.ID = C.FK_ID AND C.DEL_DT IS NULL
GROUP BY KEYS;
Upvotes: 1