Oracle中的SUM用法讲解

SELECT
AREA_CODE,
SUM (
CASE
WHEN IPTV_NBR IS NULL or IPTV_NBR = '' THEN
0
ELSE
1
END
),
SUM (
CASE
WHEN ACC_NBR IS NULL or ACC_NBR = '' THEN
0
ELSE
1
END
),
COUNT ()
FROM
GAT_SQMS.GAT_SQMS_BAND_IPTV_VIEW
GROUP BY
AREA_CODE;

SUM (
CASE
WHEN IPTV_NBR IS NULL or IPTV_NBR = '' THEN
0
ELSE
1
END
)

CREATE OR REPLACE FUNCTION SumString(i_TableName   IN VARCHAR2,
                   i_GroupColName IN VARCHAR2,
                   i_ResultColName IN VARCHAR2,
                   i_GroupColValue IN VARCHAR2,
                   i_Separator   IN VARCHAR2)
 RETURN VARCHAR2 IS
 TYPE T_Cur IS REF CURSOR;
 C_Cur  T_Cur;
 V_Sql  VARCHAR2(2000);
 V_Result VARCHAR2(2000);
 V_Tmp  VARCHAR2(200);
 V_Cnt  NUMBER := 0;
BEGIN
 V_Result := ' ';
 V_Sql  := 'SELECT  ' || i_ResultColName || '  FROM  ' || i_TableName ||
       '  WHERE  ' || i_GroupColName || '  =  ' || i_GroupColValue;
 OPEN C_Cur FOR V_Sql;
 LOOP
  FETCH C_Cur
   INTO V_Tmp;
  EXIT WHEN C_Cur%NOTFOUND;
  IF V_Cnt = 0 THEN
   V_Result := V_Tmp;
  ELSE
   V_Result := V_Result || i_Separator || V_Tmp;
  END IF;
  V_Cnt := V_Cnt + 1;
 END LOOP;
 CLOSE C_Cur;
 RETURN V_Result;
END SUMSTRING;