How to select only one row for each distinct value

Question

I have a pretty long query which includes subqueries and joins that I need to run for a report. It runs without error, but the results show 11 rows for each value, when I need it to display 1 row per value. I'm using 'select distinct', which is what I thought I needed to use to accomplish this goal, but it doesn't seem to be working. What am I doing wrong?

The value that I need to display only 1 row per each distinct occurrence is "cases.casenum".

Here's the query:

SELECT distinct
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS TOTAL_LOST_FILES, cases.casenum AS 'Case Number', user_case_data.discharged_date AS 'Discharged Date',

(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM (cases LEFT JOIN case_checklist ON cases.casenum=case_checklist.case_id)
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
ORDER BY user_case_data.discharged_date ASC;

And here's an example of the output... which makes it easy to see what we're going for- no white space, just one row for each specific value in the "case number" column.

Answer 1

SELECT 
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS "Total Lost Files", cases.casenum AS "Case Number", user_case_data.discharged_date AS "Discharged Date",

MAX(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

MAX(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

MAX(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

MAX(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

MAX(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

MAX(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

MAX(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

MAX(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM cases 
LEFT JOIN case_checklist ON cases.casenum = case_checklist.case_id
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num AND case_notes.topic LIKE 'LOS Case Status Update'
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
GROUP BY cases.casenum, user_case_data.discharged_date
ORDER BY user_case_data.discharged_date ASC;

Answer 2

You could probably PIVOT your data to turn the rows into columns

SELECT a.casenum AS [Case Number]
    , a.TotalLostFiles
    , a.discharge_date AS [Discharged Date]
    , [101] AS [Advised Attorney]
    , [LOS Case Status Update] AS [GENDOC_31_Mailed]
    , [LOS Updated Lein Ltr] AS [Sent Updated Lien Ltr]
    , [109] AS [Time Allocation Completed]
    , [110] AS [Attorney Signed Affidavit]
    , [111] AS [Lien Letters Sent]
    , [112] AS [Sent Lien to Counsel]
    , [113] AS [Received Costs and Transferred]
FROM (
    SELECT DISTINCT c.casenum
        , (
            SELECT COUNT(DISTINCT casenum)
            FROM user_case_data
            WHERE casenum = c.casenum 
                AND discharge_date >= '##START##'
                AND discharge_date <= '##END##'
        ) AS TotalLostFiles
        , cn.topic
        , cn.note_date
        , cc.code
        , cc.due_date
        , ucd.discharge_date
    FROM case c
    LEFT JOIN case_checklist cc ON cc.case_id = c.casenum
        AND cc.code IN ('101', '109', '110', '111', '112', '113')
    LEFT JOIN user_case_data ucd ON usd.casenum = c.casenum
        AND ucd.discharge_date BETWEEN '##START##' AND '##END##'
    LEFT JOIN case_notes cn ON cn.case_num = c.casenum 
        AND cn.topic IN ('LOS Case Status Update', 'LOS Updated Lein Ltr')
) a
PIVOT (
    MAX(due_date) FOR cc.code IN ([101], [109], [110], [111], [112], [113])
) b
PIVOT (
    MAX(note_date) FOR cn.topic IN ([LOS Case Status Update], [LOS Updated Lein Ltr])
) c