Oracle中的SUM用法讲解

SELECTAREA_CODE,SUM (CASEWHEN IPTV_NBR IS NULL or IPTV_NBR = '' THEN0ELSE1END),SUM (CASEWHEN ACC_NBR IS NULL or ACC_NBR = '' THEN0ELSE1END),COUNT (*)FROMGAT_SQMS.GAT_SQMS_BAND_IPTV_VIEWGROUP BYAREA_CODE;

SUM (CASEWHEN IPTV_NBR IS NULL or IPTV_NBR = '' THEN0ELSE1END)

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;