I need to improve my T-SQL query so that i can return the sum of columns

Question

Here is my current query:

    SELECT patron_name, 
    producer.federal_number, 
    hst_number, 
    average_bf_test,
    (SELECT MAX(s.statement_number) FROM statement s) AS statement_number,
    (SELECT MAX(s.period_ending) FROM statement s) AS period_ending
    FROM producer
    JOIN producer_details ON producer.federal_number = producer_details.federal_number
    JOIN statement ON producer.patron_number = statement.patron_number
    WHERE producer.patron_number = @PatronNo

I need to improve it so that i can retrieve the sum of the money columns in my 'payment' table:

 quota_payment (money)
    total_deductions (money)
    net_cheque_or_direct_deposit (money)
    interim_payment (money)
    final_payment (money)
    statement_number (int) (FK) (statement_number is the primary key in the statement table)

As per @Quassnoi's request, here is sample data and @Quassnoi's query output:

producer Table:
patron_number  patron_name  federal_number
20109      A. Beaton    44373

producer_details Table:
federal_number  hst_number  average_bf_test
44373       R999999999  0.0392

statement Table:
statement_number  period_ending  description         amount_shipped  patron_number
386               2009/07/30     Quota Milk Shipped  32.91           20109
387               2009/11/30     Quota Milk Shipped  22.34           20109

payment Table:
quota_payment  total_deductions  net_cheque_or_direct_deposit  interim_payment
22872.5800     7526.6500         15345.9300                    6520.7000
18474.3400     4563.2300         13911.1100                    3220.3000
final_payment   statement_number
8825.2300       386
10690.8100      387          

This is the data that is returned by @Quassnoi's query (originally the second row was NULLs for the money columns, however, it turns out that that was because there was only one row in the payment table at that time, but i've since added an additional row):

patron_name  federal_number  hst_number  average_bf_test       
A. Beaton    44373           R999999999   0.0392                           
A. Beaton    44373           R999999999   0.0392                             

statement_number   period_ending     quota_payment   total_deductions
387                2009/11/30        22872.5800      7526.6500
387                2009/11/30        18474.3400      4563.2300

net_cheque_or_direct_deposit   interim_payment      final_payment
15345.9300                     6520.7000            8825.2300
13911.1100                     3220.3000            10690.8100

Here is the desired recordset:

patron_name  federal_number  hst_number  average_bf_test       
A. Beaton    44373           R999999999  0.0392   

statement_number   period_ending     quota_payment   total_deductions
387                2009/11/30        41346.92        12089.88


net_cheque_or_direct_deposit   interim_payment      final_payment
29257.04                        9741.00             19516.04

I forgot to mention that the sum values for the money columns should be "year to date" values (meaning values from the previous year would not be included in the SUM results), which makes it a little more complicated.

Answer 1

If I understand what your looking to do, this should work, did not test the syntax...

based on your comment maybe your looking for:

Select a.*,sum(quota_payment) as sum_quota_payment, 
    sum(total_deductions) as sum_total_deductions,
    sum(net_cheque_or_direct_deposit) as sum_net_cheque, 
    sum(interim_payment) as sum_interim_payment, 
    sum(final_payment) as sum_final_payment

        from 
        (SELECT patron_name,  
            producer.federal_number,  
            hst_number,  
            average_bf_test, 
            (SELECT MAX(s.statement_number) FROM statement s) AS statement_number, 
            (SELECT MAX(s.period_ending) FROM statement s) AS period_ending 
            FROM producer 
            JOIN producer_details ON producer.federal_number = producer_details.federal_number 
            JOIN statement ON producer.patron_number = statement.patron_number 
            WHERE producer.patron_number = @PatronNo) 
    a left join payment ON a.statement_number = payment.statement_number

Answer 2

SELECT  patron_name, 
        producer.federal_number, 
        hst_number, 
        average_bf_test,
        (
        SELECT  MAX(s.statement_number)
        FROM    statement s
        ) AS statement_number,
        (
        SELECT MAX(s.period_ending) 
        FROM   statement s
        ) AS period_ending,
        sums.*
FROM    producer
JOIN    producer_details
ON      producer_details.federal_number = producer.federal_number
JOIN    statement so
ON      so.patron_number = producer.patron_number
CROSS APPLY
        (
        SELECT  SUM(quota_payment) AS quota_payment,
                SUM(total_deductions) AS total_deductions,
                SUM(net_cheque_or_direct_deposit) AS net_cheque_or_direct_deposit,
                SUM(interim_payment) AS interim_payment,
                SUM(final_payment) AS final_payment
        FROM    payment p
        WHERE   p.statement_number = so.statement_number
        ) sums
WHERE   producer.patron_number = @PatronNo

Answer 3

You can try this:

Select 
patron_name, 
federal_number, 
hst_number, 
average_bf_test, 
statement_number, 
period_ending, 
sum(quota_payment) as quota_payment, 
sum(total_deductions) as total_deductions, 
sum(net_cheque_or_direct_deposit) as net_cheque_or_direct_deposit, 
sum(interim_payment) as interim_payment, 
sum(final_payment) as final_payment 
from (Quassnois Query here) a 
group by 
patron_name, 
federal_number, 
hst_number, 
average_bf_test, 
statement_number, 
period_ending

That should get you what you want based on the data you posted. I don't know if this logically makes sence in your situation, but it should works.