Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Complex(?) GROUP BY issue

Complex(?) GROUP BY issue

From: Kurta <submit_at_galleus.com>
Date: 1 Apr 2004 06:26:57 -0800
Message-ID: <efcb1994.0404010626.288c6a6b@posting.google.com>


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
Received on Thu Apr 01 2004 - 08:26:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US