Oracle中的SUM用法讲解

Oracle中的SUM条件查询

1、按照区域编码分组查询区域编码、IPTV_NBR不为空的数量、ACC_NBR不为空的数量、所有用户数量

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;

2、使用sum条件查询

查询IPTV_NBR不为空的数量

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

SUM里边使用CASE WHEN 语句

当IPTV_NBR IS NULL 为0，ELSE 为1

oracle中sum字符串方法

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;