Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 7th, 2008, 12:14 AM
Authorized User
 
Join Date: Apr 2007
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default count statement

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
__________________
Yogesh
 
Old July 7th, 2008, 01:01 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old July 7th, 2008, 02:11 AM
Authorized User
 
Join Date: Apr 2007
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 July 7th, 2008, 02:04 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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:
    44
    73
   118
  NULL
  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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
XSL: Count = Count + 1 elayaraja.s XSLT 3 July 18th, 2008 03:21 AM
how to count? wkm1925 SQL Server 2000 2 February 26th, 2007 11:57 AM
is there any in built function to count page count g.tamilselvan MySQL 1 February 15th, 2006 07:43 AM
Count, sum, count a value, return records CongoGrey Access 1 April 18th, 2005 02:25 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.