SUM

Question

SUM

Curtis-211043

SSC Eights!

Points: 819
More actions
May 19, 2005 at 10:04 am

#89008

I am trying to create a stored procedure that will give me the results like so:
TransactionType
SaleAmount
PaymentAmount
Balance = Sum of SaleAmount - sum of PaymentAmount
This is how I have done the stored procedure so far. When I add the TransactionType, SaleAmount, and PaymentAmount to the statement then it doesn't do the math right or tells me I need to group them.
Select Sum(SaleAmount) - Sum(PaymentAmount) AS Balance FROM dbo.Transactions Where UserID = @ClientID

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select TransactionType, Sum(SaleAmount) as SaleAmount, Sum(PaymentAmount) as PaymentAmount, Sum(SaleAmount - PaymentAmount) AS Balance FROM dbo.Transactions Where UserID = @ClientID

group by TransactionType

Curtis-211043 SSC Eights! Points: 819 More actions · Answer 2

Thank you for replying but I am still not getting the right results.

the values in the SaleAmount and PaymentAmount are in different rows

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Can you give the output from Remi's code, because that seems very odd. Maybe include UserID; remember to use that in the GROUP BY as well...

I wasn't born stupid - I had to study.

Curtis-211043 SSC Eights! Points: 819 More actions · Answer 4

This is with the UserID in the mix

UserID TransactionType SaleAmount1 PaymentAmount1 Balance

----------- -------------------------------------------------- ----------- -------------- ----------

209541 Executive 3 Year 6200

209541 Exeuctive 1 Year 10400

209541 Payment 10800

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Can you post the definition of the tables with some sample data for each one also with the expected results of the query? We're only gonna do guess work without it.

Curtis-211043 SSC Eights! Points: 819 More actions · Answer 6

Here are the columns:

UserID, TransactionType, SaleAmount, PaymentAmount, PaymentType

UserID TransactionType SaleAmount PaymentAmount PaymentType

209541 Executive 3 Year 6200

209541 3000 Visa

209541 <null 3200 Amex

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

What are the expected results from the query with this data?

Curtis-211043 SSC Eights! Points: 819 More actions · Answer 8

I need to show each transaction and then the balance on the account. Something like a receipt.

I am going to use a datalist on my aspx page to show my results

Transaction Sale Payment

Executive 3 year 6200

Visa 3000

Balance 3200

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

This is a basic task but I just don't see a solution with the data you are presenting me...

Can you post the script to create all the tables you need in this query along with data for each table? Or if it's the same table, can you point me to the flag that tells weather the line is a payment or a bill?

Curtis-211043 SSC Eights! Points: 819 More actions · Answer 10

It is all one table. I was just going to order them by TransactionDate because the sale will always appear first.

TransactionType determines if it is a sale or payment

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Again more demo data would have been great because I'm still guessing here :

this assumes that you have only a single amount column and also assumes that you have some sort of orderid column

Select UserID, SUM(CASE when TransactionType = 'Sale' THEN AmountColumn ELSE AmountColumn * -1 END) as Balance, OrderId

from dbo.YourTable

group by UserId, OrderId

this assumes that you have one column for salesamount and another for paymentamount (still with orderid)

Select userId, OrderId, sum(SaleAmount - PaymentAmount) as Balance from dbo.YourTable

group by UserId, OrderId

noeld SSC Guru Points: 96590 More actions · Answer 12

Can you fill in the blanks ?

UserID TransactionType SaleAmount PaymentAmount PaymentType

209541 Executive 3 Year 6200

209541 3000 Visa

209541 3200 Amex

-------|----------------|-----------|--------------|------------|

-------|----------------|-----------|--------------|------------|

209541 | | | | |

-------|----------------|-----------|--------------|------------|

209541 | | | | |

-------|----------------|-----------|--------------|------------|

209541 | | | | |

-------|----------------|-----------|--------------|------------|

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Already asked him 3 times... maybe he'll actually help us help him this time...

noeld SSC Guru Points: 96590 More actions · Answer 14

I know ... that is why I tried to post a table so that he can be CLEAR about what data goes into what column!

* Noel