group by and select max with value null

Question

I have a next problem with query

SELECT 
T.DETALLE_BECA_ANIO anio,
T.DETALLE_BECA_MES mes,
T.DETALLE_BECA_NIVEL_EDU_ID edu_id,
T.DETALLE_BECA_TRAMO_ID tr_id,

MAX(
CASE
  WHEN T.DETALLE_BECA_TIPO_BENE_ID IS NULL
  THEN NVL(DETALLE_BECA_VALOR,0)
  ELSE 0
END) mant ,
MAX(
  CASE
  WHEN T.DETALLE_BECA_TIPO_BENE_ID = 1
  THEN NVL(DETALLE_BECA_VALOR,0)
  ELSE 0
  END) tras
  FROM
(SELECT DETALLE_BECA_NIVEL_EDU_ID,
  DETALLE_BECA_BECA_ID,
  DETALLE_BECA_TIPO_BENE_ID,
  DETALLE_BECA_VALOR,
  DETALLE_BECA_MES,
  DETALLE_BECA_REGION_ID,
  DETALLE_BECA_PROVINCIA_ID,
  DETALLE_BECA_ANIO,
  DETALLE_BECA_TRAMO_ID,
  DETALLE_BECA_COMUNA_ID
FROM TBL_DETALLE_BECAS

WHERE (DETALLE_BECA_TIPO_BENE_ID = 1
OR DETALLE_BECA_TIPO_BENE_ID   IS NULL)
and DETALLE_BECA_BECA_ID = 1
and detalle_beca_mes = 3
) T
GROUP BY T.DETALLE_BECA_BECA_ID,
T.DETALLE_BECA_TRAMO_ID,
T.DETALLE_BECA_REGION_ID,
T.DETALLE_BECA_PROVINCIA_ID,
T.DETALLE_BECA_ANIO,
T.DETALLE_BECA_MES,
T.DETALLE_BECA_NIVEL_EDU_ID,
T.DETALLE_BECA_COMUNA_ID
ORDER BY T.DETALLE_BECA_BECA_ID,
T.DETALLE_BECA_MES,
T.DETALLE_BECA_NIVEL_EDU_ID

output:

  "ANIO"    "MES"   "EDU_ID"    "TR_ID" "MANT"  "TRAS"
    2017      3         2        0.62     0      NULL
    2017      3         3        1.24     6      NULL 
    2017      3        NULL       1.0     NULL     1

I need that sum value where EDU_ID is null with value 2,3 in TR_ID and replace value null in "tras" with value from EDU is null

   "ANIO"   "MES"   "EDU_ID"    "TR_ID" "MANT"  "TRAS"
    2017      3         2        1.62     0      1
    2017      3         3        2.24     6      1 

I writed query with min(edu_id) or max(edu_id ) but could not solve my problem.

The other thing that occurred to me is to make a join with the same table

Answer 1

First, this makes more sense as your query:

SELECT T.DETALLE_BECA_ANIO as anio, T.DETALLE_BECA_MES as mes,
       T.DETALLE_BECA_NIVEL_EDU_ID as edu_id, T.DETALLE_BECA_TRAMO_ID as tr_id,
       MAX(CASE WHEN T.DETALLE_BECA_TIPO_BENE_ID IS NULL
                THEN NVL(DETALLE_BECA_VALOR, 0)
                ELSE 0
           END) as mant ,
       MAX(CASE WHEN T.DETALLE_BECA_TIPO_BENE_ID = 1
                THEN NVL(DETALLE_BECA_VALOR,0)
                ELSE 0
           END) tras
FROM TBL_DETALLE_BECAS
WHERE (DETALLE_BECA_TIPO_BENE_ID = 1 OR DETALLE_BECA_TIPO_BENE_ID IS NULL) AND
      DETALLE_BECA_BECA_ID = 1 AND
      detalle_beca_mes = 3
GROUP BY T.DETALLE_BECA_ANIO, T.DETALLE_BECA_MES,
         T.DETALLE_BECA_NIVEL_EDU_ID, T.DETALLE_BECA_TRAMO_ID
ORDER BY T.DETALLE_BECA_BECA_ID, T.DETALLE_BECA_MES, T.DETALLE_BECA_NIVEL_EDU_ID;

This eliminates the subquery (unnecessary) and only aggregates by the columns being returned. A proper query might fix your problem.

But, you seem to want to use NULL to be "all" for the other columns. If so, something like this will work:

WITH t as (
      SELECT T.DETALLE_BECA_ANIO as anio, T.DETALLE_BECA_MES as mes,
             T.DETALLE_BECA_NIVEL_EDU_ID as edu_id, T.DETALLE_BECA_TRAMO_ID as tr_id,
             MAX(CASE WHEN T.DETALLE_BECA_TIPO_BENE_ID IS NULL
                      THEN NVL(DETALLE_BECA_VALOR, 0)
                      ELSE 0
                 END) as mant ,
             MAX(CASE WHEN T.DETALLE_BECA_TIPO_BENE_ID = 1
                      THEN NVL(DETALLE_BECA_VALOR,0)
                      ELSE 0
                 END) tras
      FROM TBL_DETALLE_BECAS
      WHERE (DETALLE_BECA_TIPO_BENE_ID = 1 OR DETALLE_BECA_TIPO_BENE_ID IS NULL) AND
            DETALLE_BECA_BECA_ID = 1 AND
            detalle_beca_mes = 3
      GROUP BY T.DETALLE_BECA_ANIO, T.DETALLE_BECA_MES,
             T.DETALLE_BECA_NIVEL_EDU_ID, T.DETALLE_BECA_TRAMO_ID
     )
SELECT t.ANIO, t.MES, t.EDU_ID,
       COALESCE(t.TR_ID, 0) + COALESCE(tnull.TR_ID, 0) as TR_ID,
       t.MANT,
       COALESCE(t.TRAS, 0) + COALESCE(tnull.TRAS, 0) as TRAS
FROM t LEFT JOIN
     (SELECT t.*
      FROM t
      WHERE t.edu_id IS NULL
     ) tnull
     ON tnull.ANIO = t.ANIO AND tnull.MES = t.MES
WHERE t.edu_id IS NOT NULL
ORDER BY T.DETALLE_BECA_BECA_ID, T.DETALLE_BECA_MES, T.DETALLE_BECA_NIVEL_EDU_ID;