Distinct with SUM and JOINS

Transact-SQL
1

Hi I am new here and would greatly appreciate some help with this query thanks in advance. I'm trying to select member id and total score from the tables. I have 4 members all with different criteria. Member 1 has two diagnosis (2,4) Diagnosis 2 map to category 2 for a value of 20. Diagnosis 4 map to category 3 for a value of 30 sum of 50.
Member 2 does not have Diagnosis but has to be in the result set.
Member 3 has two diagnosis (3,4) Diagnosis 3 map to category 3 for a value of 30, diagnosis 4 map to category 3 for a value of 30 here is the kicker if one of the member has two diagnosis mapping to the same category I cannot sum the category this should be a value 30. Member 4 has two diagnosis (5,1) Diagnosis 5 map to category 1 for a value of 10 and Diagnosis 1 map to category 1 for a value of 10 total value of 20. I have 2 versions of the query this version give the scenario I just described.

SELECT Member.MemberID, sum( DiagnosisCategory.CategoryScore ) as TotalCategoryScore FROM Member
LEFT JOIN (
MemberDiagnosis LEFT JOIN Diagnosis ON Diagnosis.DiagnosisID = MemberDiagnosis.DiagnosisID
LEFT Join DiagnosisCategoryMap on DiagnosisCategoryMap.DiagnosisID = Diagnosis.DiagnosisID
LEFT join DiagnosisCategory on DiagnosisCategory.DiagnosisCategoryID = DiagnosisCategoryMap.DiagnosisCategoryID
)
ON Member.MemberID = MemberDiagnosis.MemberID
group by Member.MemberID
order by TotalCategoryScore

MemberID TotalCategoryScore
2 NULL
4 20
1 50
3 60
total score for 3 should be 30 because the category it maps to need to be distinct .
If I put distinct here
SELECT Member.MemberID, sum( distinct DiagnosisCategory.CategoryScore ) as TotalCategoryScore FROM Member
LEFT JOIN (
MemberDiagnosis LEFT JOIN Diagnosis ON Diagnosis.DiagnosisID = MemberDiagnosis.DiagnosisID
LEFT Join DiagnosisCategoryMap on DiagnosisCategoryMap.DiagnosisID = Diagnosis.DiagnosisID
LEFT join DiagnosisCategory on DiagnosisCategory.DiagnosisCategoryID = DiagnosisCategoryMap.DiagnosisCategoryID
)
ON Member.MemberID = MemberDiagnosis.MemberID
group by Member.MemberID
order by TotalCategoryScore
MemberID TotalCategoryScore
2 NULL
4 10
3 30
1 50

HERE IS THE DB SCRIPT
CREATE DATABASE TestDb
GO

USE TestDb
GO

CREATE TABLE Diagnosis
(
DiagnosisID INT NOT NULL,
DiagnosisDescription NVARCHAR(100) NOT NULL,
PRIMARY KEY (DiagnosisID)
);

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription )
VALUES ( 1 , 'Test Diagnosis 1' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription )
VALUES ( 2 , 'Test Diagnosis 2' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription )
VALUES ( 3 , 'Test Diagnosis 3' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription )
VALUES ( 4 , 'Test Diagnosis 4' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription )
VALUES ( 5 , 'Test Diagnosis 5' );

CREATE TABLE DiagnosisCategory
(
DiagnosisCategoryID INT NOT NULL,
CategoryDescription NVARCHAR(100) NOT NULL,
CategoryScore INT NOT NULL,
PRIMARY KEY (DiagnosisCategoryID)
);

INSERT INTO dbo.DiagnosisCategory ( DiagnosisCategoryID , CategoryDescription , CategoryScore )
VALUES ( 1 , 'Category A', 10);

INSERT INTO dbo.DiagnosisCategory ( DiagnosisCategoryID , CategoryDescription , CategoryScore )
VALUES ( 2 , 'Category B', 20);

INSERT INTO dbo.DiagnosisCategory ( DiagnosisCategoryID , CategoryDescription , CategoryScore )
VALUES ( 3 , 'Category C', 30);

CREATE TABLE DiagnosisCategoryMap
(
DiagnosisCategoryID INT NOT NULL,
DiagnosisID INT NOT NULL
);

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID )
VALUES ( 1 , 1 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID )
VALUES ( 2 , 2 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID )
VALUES ( 3 , 3 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID )
VALUES ( 3 , 4 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID )
VALUES ( 1, 5 );

CREATE TABLE dbo.Member
(
MemberID INT NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
PRIMARY KEY (MemberID)
);

INSERT INTO dbo.Member ( MemberID, FirstName, LastName )
VALUES ( 1, 'John', 'Smith');

INSERT INTO dbo.Member ( MemberID, FirstName, LastName )
VALUES ( 2, 'Jack', 'Smith');

INSERT INTO dbo.Member ( MemberID, FirstName, LastName )
VALUES ( 3, 'Will', 'Smyth');

INSERT INTO dbo.Member ( MemberID, FirstName, LastName )
VALUES ( 4, 'Tedashi', 'MakeWar');

CREATE TABLE MemberDiagnosis
(
MemberID INT NOT NULL,
DiagnosisID INT NOT NULL
);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID )
VALUES ( 1, 2);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID )
VALUES ( 1, 4);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID )
VALUES ( 3, 3);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID )
VALUES ( 3, 4);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID )
VALUES ( 4, 5);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID )
VALUES ( 4, 1);

2

If I understand correctly, I think this should be very close at least to the query you need:

SELECT M.MemberID, sum( DC.CategoryScore ) as TotalCategoryScore 
FROM dbo.Member M
LEFT JOIN (
    SELECT DISTINCT MD.MemberID, DCM.DiagnosisCategoryID
    FROM dbo.MemberDiagnosis MD
    INNER JOIN dbo.DiagnosisCategoryMap DCM on DCM.DiagnosisID = MD.DiagnosisID
) AS MD_DCM ON MD_DCM.MemberID = M.MemberID
LEFT join dbo.DiagnosisCategory DC on DC.DiagnosisCategoryID = md_DCM.DiagnosisCategoryID
GROUP BY M.MemberID
ORDER BY TotalCategoryScore
3

Thanks Scott this worked.

4

Sorry Scott it was a premature thank you. The values I'm after are as follow.
Member 2 NULL
Member 4 should be 20 Diagnosis 1 map to DiagnosisCategoryID 1 which is the value of 10 + Diagnosis 5 map to DiagnosisCategoryID 1 which is the value of 10 totaling 20

Member 3 Diagnosis 3 Map to Category 3 for a value of 30 Member 3 other Diagnosis 4 also map to Category 3 since both diagnosis map to CategoryID 3 the total should be 30 NOT 60 this is correct

Member 1 Diagnosis 2 Map to Category 2 for a value of 20 and Member 1 other Diagnosis 4 map to CategoryID 3 with a value of 30 totaling 50
I don't know how but what about using a CASE when to select the subquery?

5

This part of the subquery return MemberID and DiagnosisCategoryID
SELECT DISTINCT MD.MemberID, DCM.DiagnosisCategoryID
FROM MemberDiagnosis MD
INNER JOIN DiagnosisCategoryMap DCM ON DCM.DiagnosisID = MD.DiagnosisID
MemberID DiagnosisCategoryID
1 2
1 3
3 3
4 1
The DISTINCT causes member to only have one value which mean both diagnosis map to the same category is there a way to count the DiagnosisCategoryID and code for that value?