Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.