Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Complex(?) GROUP BY issue
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, KurtaReceived on Thu Apr 01 2004 - 08:26:57 CST