count statement

yogeshyl · July 7th, 2008, 12:14 AM

I want to count the INVH_NO appearing more than one time. Also i want the no. of times it is appearing. My query is as follows:

SELECT DISTINCT
   INVH_NO,INVH_DT,
   INVH_TXN_CODE,
   FORM_DESC,
   DECODE(ITED_TED_CODE,'BED',INVI_FC_VAL )||DECODE(ITED_TED_CODE,'BED CT3',INVI_FC_VAL)BASIC,

   SUM(CASE WHEN ITED_TED_CODE ='AED' THEN ITED_NET_LC_AMT ELSE 0 END) AS AED,
   SUM(CASE WHEN ITED_TED_CODE ='BED' THEN ITED_NET_LC_AMT ELSE 0 END) AS BED,
   SUM(CASE WHEN ITED_TED_CODE ='EDU CESS' THEN ITED_NET_LC_AMT ELSE 0 END) AS EDUCESS,
   SUM(CASE WHEN ITED_TED_CODE ='SHECESS' THEN ITED_NET_LC_AMT ELSE 0 END) AS SHECESS,
   SUM(CASE WHEN ITED_TED_CODE ='BED CT3' THEN ITED_NET_LC_AMT ELSE 0 END) AS BEDCT3,
   SUM(CASE WHEN ITED_TED_CODE ='EDU CESS CT3' THEN ITED_NET_LC_AMT ELSE 0 END) AS EDUCT3,
   SUM(CASE WHEN ITED_TED_CODE ='SHECESS CT3' THEN ITED_NET_LC_AMT ELSE 0 END) AS SHECT3,
   SUM(CASE WHEN ITED_TED_CODE = 'CST 3%' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'CST 4%' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'VAT 4%' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'VAT 12.5%' THEN ITED_NET_LC_AMT ELSE 0 END)AS TAX,
   SUM(CASE WHEN ITED_TED_CODE = 'DISC.' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'S - OCTROI' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'S-FREIGHT' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'S-INS' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'R-OFF' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'SROFF' THEN ITED_NET_LC_AMT ELSE 0 END)AS OTHER

FROM
OT_INVOICE_HEAD,
OT_INVOICE_ITEM,
OT_INVOICE_ITEM_TED,
OM_FORM
WHERE INVH_SYS_ID = INVI_INVH_SYS_ID
AND INVH_SYS_ID = ITED_H_SYS_ID
AND FORM_CODE = INVH_FORM_CODE
AND INVH_DT BETWEEN '01-MAY-2008' AND '31-MAY-2008'
AND INVH_TXN_CODE NOT IN ('SINVEXP')
GROUP BY INVH_NO,INVH_DT,INVH_TXN_CODE,INVH_FORM_CODE,INVI_ QTY,INVI_ITEM_CODE,FORM_DESC,
         INVI_FC_VAL,ITED_TED_CODE,ITED_NET_LC_AMT
ORDER BY INVH_NO

Yogesh

Old Pedant · July 7th, 2008, 01:01 AM

I don't understand your question, but I see room for improvement in that query.

Code:

Instead of doing

   SUM(CASE WHEN ITED_TED_CODE = 'CST 3%' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'CST 4%' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'VAT 4%' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'VAT 12.5%' THEN ITED_NET_LC_AMT ELSE 0 END)AS TAX,    

Why not just

   SUM(CASE WHEN ITED_TED_CODE IN ('CST 3%','CST 4%','VAT 4%','VAT 12.5%') 
            THEN ITED_NET_LC_AMT ELSE 0 END)AS TAX,    

And then ditto, instead of 

   SUM(CASE WHEN ITED_TED_CODE = 'DISC.' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'S - OCTROI' THEN ITED_NET_LC_AMT ELSE 0 END)+ 
   SUM(CASE WHEN ITED_TED_CODE = 'S-FREIGHT' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'S-INS' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'R-OFF' THEN ITED_NET_LC_AMT ELSE 0 END)+
   SUM(CASE WHEN ITED_TED_CODE = 'SROFF' THEN ITED_NET_LC_AMT ELSE 0 END)AS OTHER

Why not

   SUM(CASE WHEN ITED_TED_CODE IN ('DISC.','S - OCTROI','S-FREIGHT','S-INS','R-OFF','SROFF') 
            THEN ITED_NET_LC_AMT ELSE 0 END) AS OTHER

yogeshyl · July 7th, 2008, 02:11 AM

Thanks for your suggestion. The result of the statament
DECODE(ITED_TED_CODE,'BED',INVI_FC_VAL )||DECODE(ITED_TED_CODE,'BED CT3',INVI_FC_VAL)BASIC

gets displayed more than once for each result of the CASE-THEN statement's result in the query.

Yogesh

Old Pedant · July 7th, 2008, 02:04 PM

Well, sure...you *DID* write
SELECT DISTINCT
didn't you???

If ITED_TED_CODE is either 'BED' or 'BED CT3' then you will get every value of INVI_FC_VAL that is found. Plus, when ITED_TED_CODE is *not* either of those values, you will have a value of NULL.

Example:

Code:

ITED_TED_CODE   INVI_FC_VAL   [resulting value of BASIC]
   BED           73            73
   BED CT3       118           118
   AED           999           NULL
   BED           44            44
   SHECESS       333           NULL

So you will end up with values of

Code:

for your BASIC field and all of those are DISTINCT so of course you can't possibly get fewer than 4 records.

By they way, WHY did you code

Code:

DECODE(ITED_TED_CODE,'BED',INVI_FC_VAL )||DECODE(ITED_TED_CODE,'BED CT3',INVI_FC_VAL)BASIC,

instead of simply

Code:

DECODE(ITED_TED_CODE,'BED',INVI_FC_VAL,'BED CT3',INVI_FC_VAL) AS BASIC,

Or perhaps instead of

Code:

(CASE WHEN ITED_TED_CODE IN ('BED','BED CT3') THEN INVI_FC_VAL ELSE NULL END) AS BASIC

???

I've never used Oracle and had to lookup what DECODE is for, so if there's some weird Oracle reason to code as you did, apologies.