|
View previous topic :: View next topic
|
| Author |
Message |
Sandesh Kirodian
New User
Joined: 29 Sep 2011
Posts: 6
Location: India
|
|
|
|
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  |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Sandesh Kirodian
New User
Joined: 29 Sep 2011
Posts: 6
Location: India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
SELECT SUM(X.CLUS_POLS_TAKEN_NO) FROM
references table x.
where is table x? |
|
| Back to top |
|
|
Sandesh Kirodian
New User
Joined: 29 Sep 2011
Posts: 6
Location: India
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Sandesh Kirodian
New User
Joined: 29 Sep 2011
Posts: 6
Location: India
|
|
|
|
Hi Guy ,
thanks a lot for your help  
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
|
|
