Explanation of the usage of SUM in Oracle

SUM conditional query in Oracle

1. Query region codes according to region codes, the number of IPTV_NBR not empty, the number of ACC_NBR not empty, and the number of all users

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

2. Use sum conditional query

Query the number of IPTV_NBR that is not empty

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

Use CASE WHEN statements in SUM

When IPTV_NBR IS NULL is 0, ELSE is 1.

Sum string method in oracle

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 Tunable Curse; V_Sql VARCHAR2 (2000); V_Result VARCHAR2 (2000); V_Tmp VARCHAR2 (2000); V_Cnt NUMBER: = 0 BEGIN V_Result: =''; V_Sql: = 'SELECT' | | i_ResultColName | | 'FROM' | | i_TableName | | 'WHERE' | | i_GroupColName | |'='| iTunes GroupColValue; OPEN C_Cur FOR vested Sql; LOOP FETCH C_Cur INTO vaulted TMPs; EXIT WHEN curse% NOTFOUNDD; IF V_Cnt = 0 THEN V_Result: = VentTmp; ELSE V_Result: = V_Result | | i_Separator | VentTmp; END IF; V_Cnt: = V_Cnt + 1; END LOOP; CLOSE Cure Curse; RETURN Venture ResultThere end SUMSTRING

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.