sql stored procedure for Crystal Report 1

[RobbieB]

I hope I'm posting in the right area. I have been writing Cystal Reports for many years but am NEW TO SQL AND STORED PROCEDURES. I have written the attached SQL statement and need to get a sum of the AXS_ACCOUNT.ACCOUNT_BALANCE for each customer (AXS_VIEW_PAYMENT.CUSTOMER_NO) in my stored procedure. Whenever I add SUM to my statement it give me an error about my where clause. As I said I am new to Transact SQL and any help you can give would be appreciated.

SELECT AXS_VIEW_PAYMENT.PAY_METHOD_ID, AXS_VIEW_PAYMENT.CUSTOMER_NO, AXS_VIEW_PAYMENT.AMOUNT, AXS_CUSTOMER.LAST_NAME, AXS_CUSTOMER.FULL_NAME, AXS_CUSTOMER.CUSTOMER_NO AS Expr1, AXS_CUSTOMER.FIRST_NAME, AXS_ACCOUNT.ACCOUNT_BALANCE
FROM AXS_VIEW_PAYMENT INNER JOIN AXS_CUSTOMER ON AXS_VIEW_PAYMENT.CUSTOMER_NO = AXS_CUSTOMER.CUSTOMER_NO INNER JOIN
AXS_ACCOUNT ON AXS_VIEW_PAYMENT.CUSTOMER_NO = AXS_ACCOUNT.CUSTOMER_NO
WHERE (AXS_VIEW_PAYMENT.PAY_METHOD_ID = 'RSVP')

Thanks,
RobbieB

 

[gmmastros]

Something like this...

SELECT AXS_VIEW_PAYMENT.PAY_METHOD_ID, 
       AXS_VIEW_PAYMENT.CUSTOMER_NO, 
       AXS_VIEW_PAYMENT.AMOUNT, 
       AXS_CUSTOMER.LAST_NAME,              
       AXS_CUSTOMER.FULL_NAME, 
       AXS_CUSTOMER.CUSTOMER_NO AS Expr1, 
       AXS_CUSTOMER.FIRST_NAME, 
       AXS_ACCOUNT.ACCOUNT_BALANCE[!],
       Totals.TotalAccountBalance[/!]
FROM   AXS_VIEW_PAYMENT 
       INNER JOIN AXS_CUSTOMER 
         ON AXS_VIEW_PAYMENT.CUSTOMER_NO = AXS_CUSTOMER.CUSTOMER_NO 
       INNER JOIN AXS_ACCOUNT 
         ON AXS_VIEW_PAYMENT.CUSTOMER_NO = AXS_ACCOUNT.CUSTOMER_NO
[!]       INNER JOIN (
         Select AXS_ACCOUNT.Customer_No,
                Sum(AXS_ACCOUNT.ACCOUNT_BALANCE) As TotalAccountBalance
         From   AXS_ACCOUNT
         Group By AXS_ACCOUNT.Customer_No
         ) As Totals On AXS_ACCOUNT.Customer_No = Totals.Customer_No[/!]
WHERE  (AXS_VIEW_PAYMENT.PAY_METHOD_ID = 'RSVP')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RobbieB]

Thanks George, so far this looks like it will work for me.
Rob

 

[gmmastros]

Do you want me to explain why it works?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RobbieB]

Well, that would be great. I was going to try to take a look at it later to make sure I understand why but It looks like you created an "alias" (probably not the right word) for Totals.TotalAccountBalance then created a sub query selecting the fields I needed to sum the account_Balance and grouped on customer number so it would know where to put the summary field (?) and called the sub query Totals to match the "alias" you created earlier.
Is that even close?

 

[RobbieB]

Thanks again George. This did work for me perfectly. Was my synopsis of why it worked accurate?

 

[gmmastros]

Oh... sorry. I meant to reply yesterday, but I got distracted.

Anyway, your understanding of the query is good. I'm glad it worked for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom