Search code examples
sqljoinsubquerysybase

SQL - How to use GROUP BY to select 1 row for each distinct value

I'm writing a pretty long report, which runs without error, but even though I'm using 'select distinct' it's still showing 11 rows for each unique value i'm trying to report on.

The unique value that I need to display only 1 row per each distinct occurrence is case number, or 'cases.casenum'.
I've included a screenshot of the output which gives a pretty clear idea of what I'm going for... here's the code:

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;

output

Additional info:
-Each case number only has 1 discharge date. So for each row, there should be only 1 case number and 1 discharge date.
-There can be many records that exist per case number on the case_notes and case_checklist tables, however I'm only trying to pull the date for 1 single note (with topic specified in case statement), and 1 single checklist item (again specified in case statement)
-The first column isn't really necessary and there's no reason why I want to count the total number of cases for each row... I just wanted a total count somewhere in the output and didn't know how else I could do it.

Solution

  • As suggested by JustinStolle, he was correct in his suggestion for aggregate functions and a GROUP BY:

    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;