Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To get a Sum of a coloumn based on the distinct of the other

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Thu Sep 29, 2011 1:41 pm    Post subject: To get a Sum of a coloumn based on the distinct of the other
Reply with quote

I would like to know how would we achive this using Db2 Sql Query -to sum a field based off another field being distinct (one you are not summing).

Code:
Account_Number           |  Balance     
        11111                 |  100         
        11111                 |  100         
        22222                 |  500         
        22222                 |  500         
        22222                 |  500         
        33333                 |  100

The result i want to achieve is -
SUM OF BALANCE
------------
700
i.e only unique Account_Numbers should be picked and summed on balance.

i tried using select sum(distinct balance) from Account - but its not correct icon_sad.gif
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 29, 2011 2:17 pm    Post subject:
Reply with quote

just do it in 2 steps :
First get a distinct list of accno, balance (in the assumption that for the same accno, balance is also the same)
and then sum that list :

Code:
select sum(balance) from
(select AccNo, Balance from mytable group by accno,balance) A
Back to top
View user's profile Send private message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Thu Sep 29, 2011 3:47 pm    Post subject:
Reply with quote

Hi Guy,
my actual query is like this tht was just a spoof i had put in order to understand.

i tried the above method in the below query but its not working
Code:
DSNT408I SQLCODE = -206, ERROR:  X.CLUS_POLS_TAKEN_NO IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER   
DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE                           
DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR               
DSNT416I SQLERRD    = -600 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION       
DSNT416I SQLERRD    = X'FFFFFDA8'  X'00000000'  X'00000000'  X'FFFFFFFF'   
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION               

SELECT SUM(X.CLUS_POLS_TAKEN_NO) FROM                           
   (  SELECT X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,                     
            X.CLUS_POLS_TAKEN_NO  AS CLUSTER_NO                 
 FROM DB2SEC2.TLI_BEN_QUOTE X,                                   
      DB2SEC2.TLI_POL_CLAIM Y,                                   
      DB2SEC2.TLI_POL Z                                         
 WHERE X.POL_NO = Y.POL_NO                                       
   AND   
   X.POL_NO=Z.POL_NO                                                                       
   AND Z.POL_STUS_CDE = 'IF'                                     
   AND X.PART_CLM_CLUS_IND = 'Y'                                 
   AND Y.POL_CLM_STAT_CDE = 'CC'                                 
   AND X.CLM_CLIENT_ID = Y.CLM_CLIENT_ID                         
   AND X.CLAIM_QUOTE_SEQ_NO = Y.CLAIM_QUOTE_SEQ_NO               
   GROUP BY X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,X.CLUS_POLS_TAKEN_NO) X

In the above query i just want the pol_no and the sum of the cluster no
based on distinct sequence no
please help
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 29, 2011 4:21 pm    Post subject:
Reply with quote

SELECT SUM(X.CLUS_POLS_TAKEN_NO) FROM

references table x.
where is table x?
Back to top
View user's profile Send private message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Thu Sep 29, 2011 4:34 pm    Post subject:
Reply with quote

Hi,

I din't understand that part so i had coded like that i just replaced it with the table name and got this -

Please let me know is this how it should be or i am goin wrong somewhere with the syntax

Code:
   SELECT SUM(CLUS_POLS_TAKEN_NO) FROM                                         
      (  SELECT X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,                                 
               X.CLUS_POLS_TAKEN_NO  AS CLUSTER_NO                             
    FROM DB2SEC2.TLI_BEN_QUOTE X,                                               
         DB2SEC2.TLI_POL_CLAIM Y,                                               
         DB2SEC2.TLI_POL Z                                                     
    WHERE X.POL_NO = Y.POL_NO                                                   
      AND X.POL_NO = Z.POL_NO                                                   
      AND Z.POL_STUS_CDE = 'IF'                                                 
      AND X.PART_CLM_CLUS_IND = 'Y'                                             
      AND Y.POL_CLM_STAT_CDE = 'CC'                                             
      AND X.CLM_CLIENT_ID = Y.CLM_CLIENT_ID                                     
      AND X.CLAIM_QUOTE_SEQ_NO = Y.CLAIM_QUOTE_SEQ_NO                           
      GROUP BY X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,X.CLUS_POLS_TAKEN_NO)             
      DB2SEC2.TLI_BEN_QUOTE                                                     
      ;;                                                                       
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT   
         BE LEGAL ARE: FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE       
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 502 0  0  -1  1022  0 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'000003FE'  X'00000000' SQL DIAGNOSTIC INFORMATION
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 29, 2011 4:52 pm    Post subject:
Reply with quote

You have two tables X : one in your inner query and one in outer. I have renamed the outer to X2.
Code:
SELECT SUM(X2.CLUSTER_NO )
FROM ( SELECT X.CLAIM_QUOTE_SEQ_NO
          ,X.POL_NO
          ,X.CLUS_POLS_TAKEN_NO AS CLUSTER_NO
       FROM DB2SEC2.TLI_BEN_QUOTE X,
           DB2SEC2.TLI_POL_CLAIM Y,
           DB2SEC2.TLI_POL Z
      WHERE X.POL_NO = Y.POL_NO
        AND X.POL_NO=Z.POL_NO
        AND Z.POL_STUS_CDE = 'IF'
        AND X.PART_CLM_CLUS_IND = 'Y'
        AND Y.POL_CLM_STAT_CDE = 'CC'
        AND X.CLM_CLIENT_ID = Y.CLM_CLIENT_ID
         AND X.CLAIM_QUOTE_SEQ_NO = Y.CLAIM_QUOTE_SEQ_NO
    GROUP BY X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,X.CLUS_POLS_TAKEN_NO) X2

it seems to me that by joining you yourself created this multiple occurences of the X-rows.
maybe it would be better solving that by rewriting the inner query
Code:
SELECT sum(X.CLUS_POLS_TAKEN_NO) AS CLUSTER_NO
  FROM DB2SEC2.TLI_BEN_QUOTE X
  WHERE exists( select 1 from DB2SEC2.TLI_POL_CLAIM Y
               where X.POL_NO = Y.POL_NO
               AND X.CLM_CLIENT_ID = Y.CLM_CLIENT_ID
               AND X.CLAIM_QUOTE_SEQ_NO = Y.CLAIM_QUOTE_SEQ_NO
               AND Y.POL_CLM_STAT_CDE = 'CC' )
    AND exists (select 1 from DB2SEC2.TLI_POL Z 
              where X.POL_NO=Z.POL_NO
               AND Z.POL_STUS_CDE = 'IF' )
   AND X.PART_CLM_CLUS_IND = 'Y'
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 29, 2011 4:57 pm    Post subject:
Reply with quote

previous post is for a grand total. if you want totals per pol_no :
Code:
SELECT X.POL_NO ,sum(X.CLUS_POLS_TAKEN_NO) AS CLUSTER_NO
  FROM DB2SEC2.TLI_BEN_QUOTE X
  WHERE exists( select 1 from DB2SEC2.TLI_POL_CLAIM Y
               where X.POL_NO = Y.POL_NO
               AND X.CLM_CLIENT_ID = Y.CLM_CLIENT_ID
               AND X.CLAIM_QUOTE_SEQ_NO = Y.CLAIM_QUOTE_SEQ_NO
               AND Y.POL_CLM_STAT_CDE = 'CC' )
    AND exists (select 1 from DB2SEC2.TLI_POL Z 
              where X.POL_NO=Z.POL_NO
               AND Z.POL_STUS_CDE = 'IF' )
   AND X.PART_CLM_CLUS_IND = 'Y'
group by X.POL_NO
Back to top
View user's profile Send private message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Thu Sep 29, 2011 7:08 pm    Post subject:
Reply with quote

Hi Guy ,
thanks a lot for your help icon_smile.gif icon_biggrin.gif

Here is the query that worked for me -
Code:
SELECT SUM(A.CLUSTER_NO) ,A.POL_NO FROM                         
     (SELECT X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,                     
            X.CLUS_POLS_TAKEN_NO  AS CLUSTER_NO                 
 FROM DB2SEC2.TLI_BEN_QUOTE X,                                   
      DB2SEC2.TLI_POL_CLAIM Y,                                   
      DB2SEC2.TLI_POL Z                                         
 WHERE X.POL_NO = Y.POL_NO                                       
   AND X.POL_NO = Z.POL_NO                                       
   AND Z.POL_STUS_CDE = 'IF'                                     
   AND X.PART_CLM_CLUS_IND = 'Y'                                 
   AND Y.POL_CLM_STAT_CDE = 'CC'                             
   AND X.CLM_CLIENT_ID = Y.CLM_CLIENT_ID                         
   AND X.CLAIM_QUOTE_SEQ_NO = Y.CLAIM_QUOTE_SEQ_NO               
   GROUP BY X.CLAIM_QUOTE_SEQ_NO,X.POL_NO,X.CLUS_POLS_TAKEN_NO) A
  GROUP BY A.POL_NO
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Sep 29, 2011 7:11 pm    Post subject: Reply to: To get a Sum of a coloumn based on the distinct of
Reply with quote

did You notice the difference in posting format in the replies You get
and how your initial post was changed

learn to use the code tags it will make the posts more readable and will save some work to moderators to fix them .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
This topic is locked: you cannot edit posts or make replies. Job to wait and continue based on reply yugendran COBOL Programming 5 Wed Oct 05, 2016 1:46 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us