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
 

 

Query needed to Sum a column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Fri Nov 28, 2008 11:31 pm    Post subject: Query needed to Sum a column
Reply with quote

Hi All,

I have 4 columns in a table viz Col A, Col B, Col C & Amount
I want to find the total sum of amount column given that if the combination of (COl A, Col B, COl C ) have multiple instance then it should be taken only once
For Example

COl A ---------- COl B---------Col C----------Amt
AX BX CX 100
AX BX CX 100
AX BX DX 100
AX KX CX 100

Should return 300
Back to top
View user's profile Send private message

sivatechdrive

Active User


Joined: 17 Oct 2004
Posts: 190
Location: hyderabad

PostPosted: Sat Nov 29, 2008 3:21 pm    Post subject: HI
Reply with quote

Hi,

I suppose we can use the query as below
SELECT SUM(Amt),
COl A
,COl B
,Col C
FROM
GROUP BY COL A,COL B,COL C
The result would be as below

AX BX CX 200 (since ther are 2 rows wiht column values of AX BX CX)
AX BX DX 100
AX KX CX 100


cheers..prasad
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Nov 30, 2008 4:28 am    Post subject:
Reply with quote

Hello,

Quote:
I suppose we can use the query as below
I suppose maybe not. Please compare your "solution" with the requirement. . .

Your query will return 3 sums that total 400. The request is for one sum that totals 300 because the duplicate should not be summed.

It is best to ensure that replies meet the requirement.
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Sun Nov 30, 2008 4:34 pm    Post subject: Reply to: Query needed to Sum a column
Reply with quote

Hi All,

Thanks for reply.
I got the solution & the query i used was -
select sum(amt)
from tab1
where exists
(select distinct col A,Col B,Col C from Tab1)

Regards,
Tushar
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Nov 30, 2008 10:30 pm    Post subject:
Reply with quote

Hello Tushar,

What should happen if the duplicates have different amounts? In your example what if one of the AX BX CX rows had 100 for the amount and the other 250 for the amount? Seems questionable the one or the other would arbitrarily be skipped. . .
Back to top
View user's profile Send private message
lenygold

New User


Joined: 22 Oct 2008
Posts: 3
Location: baltimore

PostPosted: Mon Dec 01, 2008 2:32 am    Post subject: Reply to: Query needed to Sum a column
Reply with quote

THIS QUERY IS WRONG:
WITH T1(C1,C2,C3,Amt) AS
(VALUES('AX','BX','CX', 100),
('AX','BX', 'CX',100),
('AX','BX','DX', 100),
('AX','KX','CX', 100))
select sum(amt)
from T1
where exists
(select distinct C1,C2,C3 from T1);


1
-----------
400

1 record(s) selected.



THIS QUERY IS PRODUCING REQUESTED RESULT:

WITH T1(C1,C2,C3,Amt) AS
(VALUES('AX','BX','CX', 100),
('AX','BX','CX', 100),
('AX','BX','DX', 100),
('AX','KX','CX', 100)),
T2(C1,C2,C3,Amt) AS
(SELECT DISTINCT C1,C2,C3,AMT FROM T1)
SELECT SUM(AMT) AS DISTINCT_SUM FROM T2;
DISTINCT_SUM
------------
300

1 record(s) selected.
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Mon Dec 01, 2008 10:06 am    Post subject: Reply to: Query needed to Sum a column
Reply with quote

Hi Dick,

The amount can't be different. if col A,col B,col C values are equal then amount has to be equal.
Now the question arises why values are being repeated.
Actually this is just a part of table. In our application, if the input is not according to our need we reject it and put it into table. Now the reasons for reject can be different. In that case there will be different rows and in those rows Col A,Col B,Col C & Amount will be same.

I don't know what is wrong with my query because when i validated it, it was giving correct amount. Anyways i will re-run the query & let you know.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Dec 01, 2008 10:46 am    Post subject:
Reply with quote

Hello Tushar,

Quote:
if the input is not according to our need we reject it and put it into table.
I understand having a "rejects" table.

I'm confused why some of the rejects should be skipped when the amounts are summed (even if they are all the same amount) icon_confused.gif

How does the "cleanup" process for the rejects table deal with the "extra" rows or are they all simply discardrd at some point? If this is getting too far away from your question, we don't need to go any deeper - i was just curious icon_smile.gif
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Mon Dec 01, 2008 11:03 am    Post subject: Reply to: Query needed to Sum a column
Reply with quote

Hi Dick,

In Any application you need mulitple data.
Lets suppose we take the banking application in which you are sending the statement to customer.
For this you need Account Number, Address & Name of Customer.
Now there can be a case where we get only address. Account number & Customer Name is spaces.
So in our case this will go a two different row. One row will state that reject due to account number missing & another stating name missing.
Now for both these row the bank statement will be same(or total deposit/transaction). So when you are picking this row, you need to pick only one amount.

As for the cleanup process its like delete the rows after x number of years.

hope i am clear this time.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Mon Dec 01, 2008 11:14 am    Post subject: Reply to: Query needed to Sum a column
Reply with quote

that' Your organization's logic, but the main point is ..
what are the guarentees that the so called amount is the same
a wrong record cannot claim to be partly right..

as long as Your banking customers are happy icon_eek.gif
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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