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
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
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. . .
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
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.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
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)
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
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.
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..
.jpg)
.jpg)
