Re: Complex(?) GROUP BY issue

Kurta wrote:

> I have a select query (Q1):
> SELECT
> MBUR_ID, USR_MRGEMSTR, USR_C4C_ID
> (SELECT COUNT(UMET_ID) FROM T_UM_BASE WHERE MBUR_ID = UMBS_MEMB)
> UMET,
> (SELECT COUNT(CMPR_ID) FROM T_CM_BASEBASE WHERE MBUR_ID = CMBS_MEMB)
> CMPR,
> (SELECT COUNT(CASE_ID) FROM T_CM_BASEBASE WHERE MBUR_ID = CMBS_MEMB)
> CASE_,
> (SELECT COUNT(MCLM_ID) FROM T_MDCLCLM WHERE MBUR_ID = MCLM_PAT_ID)
> MCLM,
> (SELECT COUNT(DCLM_ID) FROM T_DRUGCLM WHERE MBUR_ID = DCLM_PAT_ID)
> DCLM,
> MBUR_PCP_PVDR PCP
> FROM
> T_USR
> WHERE
> MBUR_ID IN (81, 82)
> ORDER BY
> MBUR_ID
>
> This collects users, counts some of their related records and selects
> some data.
> "MBUR_ID" "USR_MRGEMSTR" "USR_C4C_ID" "UMET" "CMPR" "CASE_" "MCLM" "DCLM" "PCP"
> 81 null "CHME001" 0 0 0 10 1 null
> 82 81 "CHME002" 0 1 2 2 0 null
>
> Now, I merged user 82 to 81, this means that there's a link from 82 to
> 81 (slave-master). I have to return the same data, but grouping by
> master, aggregating some columns. The problem is that some columns
> must not show aggregate data but the data from the master row.
>
> The query looks like this:
> SELECT NVL(USR_MRGEMSTR, MBUR_ID), ???, SUM(UMET), SUM(CMPR),
> SUM(CASE_), SUM(MCLM), SUM(DCLM), MIN(PCP) FROM (
> --
> --- Paste Q1 here
> --
> ) GROUP BY NVL(USR_MRGEMSTR, MBUR_ID)
>
> "NVL(USR_MRGEMSTR,MBUR_ID)" ??? "SUM(UMET)" "SUM(CMPR)" "SUM(CASE_)" "SUM(MCLM)" "SUM(DCLM)" "MIN(PCP)"
> 81 "CHME001" 0 1 2 12 1 null
>
> I'd want to have "CHME001" as "USR_C4C_ID". But this is not guaranteed
> to be the first of the group, its USR_MRGEMSTR is either NULL or
> equals its MBUR_ID. I'm looking for the expression to replace ???. If
> there's a way to do it.
>
> Thanks,
>
> Kurta

Solve this problem by returning to basic relational theory rather than using duct tape and paper clips: Fix the design.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)