need query to calculate amount
-
Hi, I have a table like Sno ReceiptNo CustomerId Amount Date commissionAmount AgentId 1 10 C96 5000 02/02/2009 500 A20 2 10 C96 5000 02/02/2009 300 A46 3 10 C96 5000 02/02/2009 200 A37 4 11 C50 3000 02/03/2009 300 A35 5 11 C96 2000 02/03/2009 200 A43 here one receiptid have multiple records with same customer. this table contains multiple records with the same receiptno and customer id.but i want to add distinct amount of each receipt of perticular custimer. means here customer C96 total paid amount is 5000+2000 i.e 7000. I want query for this.If any one give the answer it will be very useful for me . Thanks in advance. Haritha.
Haritha
-
Hi, I have a table like Sno ReceiptNo CustomerId Amount Date commissionAmount AgentId 1 10 C96 5000 02/02/2009 500 A20 2 10 C96 5000 02/02/2009 300 A46 3 10 C96 5000 02/02/2009 200 A37 4 11 C50 3000 02/03/2009 300 A35 5 11 C96 2000 02/03/2009 200 A43 here one receiptid have multiple records with same customer. this table contains multiple records with the same receiptno and customer id.but i want to add distinct amount of each receipt of perticular custimer. means here customer C96 total paid amount is 5000+2000 i.e 7000. I want query for this.If any one give the answer it will be very useful for me . Thanks in advance. Haritha.
Haritha
HI Haritha......... SELECT SUM(amount) FROM customer WHERE ReciptNo IN (SELECT DISTINCT ReceiptNo,amount FROM customer WHERE CustomerId='C96') :rose:najmalccl@gmail.com
-
Hi, I have a table like Sno ReceiptNo CustomerId Amount Date commissionAmount AgentId 1 10 C96 5000 02/02/2009 500 A20 2 10 C96 5000 02/02/2009 300 A46 3 10 C96 5000 02/02/2009 200 A37 4 11 C50 3000 02/03/2009 300 A35 5 11 C96 2000 02/03/2009 200 A43 here one receiptid have multiple records with same customer. this table contains multiple records with the same receiptno and customer id.but i want to add distinct amount of each receipt of perticular custimer. means here customer C96 total paid amount is 5000+2000 i.e 7000. I want query for this.If any one give the answer it will be very useful for me . Thanks in advance. Haritha.
Haritha
Haritha, use the following query this will help you: Select sum(Amount) as Amount_Sum from Customer group by CustomerId
-
Hi, I have a table like Sno ReceiptNo CustomerId Amount Date commissionAmount AgentId 1 10 C96 5000 02/02/2009 500 A20 2 10 C96 5000 02/02/2009 300 A46 3 10 C96 5000 02/02/2009 200 A37 4 11 C50 3000 02/03/2009 300 A35 5 11 C96 2000 02/03/2009 200 A43 here one receiptid have multiple records with same customer. this table contains multiple records with the same receiptno and customer id.but i want to add distinct amount of each receipt of perticular custimer. means here customer C96 total paid amount is 5000+2000 i.e 7000. I want query for this.If any one give the answer it will be very useful for me . Thanks in advance. Haritha.
Haritha
Either of these will work if using sql server
with temp (ReceiptNo, CustomerID, Amount)
as
(
SELECT ReceiptNo, CustomerID, Amount
FROM customer
group by ReceiptNo, CustomerID, Amount
)
select CustomerID, sum(Amount) from temp group by CustomerID
GO/*** or this one ***/
SELECT ReceiptNo, CustomerID, Amount
INTO [#temp]
FROM customer
GROUP BY ReceiptNo, CustomerID, Amount
select CustomerID, sum(Amount) from #temp group by CustomerID
GOdrop table #temp
GO -
Hi, I have a table like Sno ReceiptNo CustomerId Amount Date commissionAmount AgentId 1 10 C96 5000 02/02/2009 500 A20 2 10 C96 5000 02/02/2009 300 A46 3 10 C96 5000 02/02/2009 200 A37 4 11 C50 3000 02/03/2009 300 A35 5 11 C96 2000 02/03/2009 200 A43 here one receiptid have multiple records with same customer. this table contains multiple records with the same receiptno and customer id.but i want to add distinct amount of each receipt of perticular custimer. means here customer C96 total paid amount is 5000+2000 i.e 7000. I want query for this.If any one give the answer it will be very useful for me . Thanks in advance. Haritha.
Haritha
Why do these look like sports bookmaking[^] transactions? :suss:
-
Either of these will work if using sql server
with temp (ReceiptNo, CustomerID, Amount)
as
(
SELECT ReceiptNo, CustomerID, Amount
FROM customer
group by ReceiptNo, CustomerID, Amount
)
select CustomerID, sum(Amount) from temp group by CustomerID
GO/*** or this one ***/
SELECT ReceiptNo, CustomerID, Amount
INTO [#temp]
FROM customer
GROUP BY ReceiptNo, CustomerID, Amount
select CustomerID, sum(Amount) from #temp group by CustomerID
GOdrop table #temp
GOWhy the H are you using temporary tables for that? :confused:
-
Hi, I have a table like Sno ReceiptNo CustomerId Amount Date commissionAmount AgentId 1 10 C96 5000 02/02/2009 500 A20 2 10 C96 5000 02/02/2009 300 A46 3 10 C96 5000 02/02/2009 200 A37 4 11 C50 3000 02/03/2009 300 A35 5 11 C96 2000 02/03/2009 200 A43 here one receiptid have multiple records with same customer. this table contains multiple records with the same receiptno and customer id.but i want to add distinct amount of each receipt of perticular custimer. means here customer C96 total paid amount is 5000+2000 i.e 7000. I want query for this.If any one give the answer it will be very useful for me . Thanks in advance. Haritha.
Haritha
select distinct receiptno from tablename... i will displays all records without duplicates then u can fine total amount of all records ...or particular customer records...