Sql Server aggregation problem

I have an interesting problem.  I have to present data with a single sql statement from invoice header and invoice transaction tables.

Here's my current SQL statement:
select 
Base.CASENO as [Matter Number],
INVOICES.INV_NO as [Invoice Number],
sum(INVTRANS.TRANS_AMT) as [Total Fees],
ISNULL(INVTRANS.TRANS_HOURS, '') as [Units],
CASE
	WHEN INVTRANS.TRANS_HOURS IS NULL THEN 0
	WHEN INVTRANS.TRANS_HOURS = 0 THEN 0
	ELSE INVTRANS.TRANS_AMT/INVTRANS.TRANS_HOURS 
END as [Rate],
INVTRANS.TRANS_AMT as [Item Total],
INVTRANS.INVTRANS_ID
from INVTRANS inner join INVOICES on INVTRANS.INVOICE_ID = INVOICES.INVOICE_ID
left outer join BASE on INVTRANS.MATTER_ID = BASE.MATTER_ID
group by
	base.CASENO, invoices.INV_NO, INVTRANS.TRANS_HOURS, INVTRANS.TRANS_AMT, INVTRANS.INVTRANS_ID
order by 2, 1

Open in new window


Here's the result I get from my data:
SQL table result
The problem is that the Total Fees column should be the sum of that column, where the invoice number and matter number are the same, redundantly shown for each row.  For example, in my image above, rows 5 & 6 are the same invoice number and matter number, and so each should show 60.00 (the total of Item Total for both rows).  And rows 8 and 9 should each have 150.00 as Total Fees, since they have the same invoice number and matter number.

Normally, this would be the invoice header record's total, but in this instance, my client needs the equivalent of a subtotal by matter number within the invoice numbers.

any ideas?
bjones8888PresidentAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
If I have understood your request I think sum(..) over (partiton by ...) would suit the need. Please try this
SELECT
      Base.CASENO AS [Matter Number]
    , INVOICES.INV_NO AS [Invoice Number]
    , sum(INVTRANS.TRANS_AMT) AS [Total Fees]
    , sum(INVTRANS.TRANS_AMT) OVER (
            PARTITION BY INVOICES.INV_NO, Base.CASENO
            ) AS [Total Fees Per Inv/Matter]
    , ISNULL(INVTRANS.TRANS_HOURS, '') AS [Units]
    , CASE 
        WHEN INVTRANS.TRANS_HOURS IS NULL
            THEN 0
        WHEN INVTRANS.TRANS_HOURS = 0
            THEN 0
        ELSE INVTRANS.TRANS_AMT / INVTRANS.TRANS_HOURS
        END AS [Rate]
    , INVTRANS.TRANS_AMT AS [Item Total]
    , INVTRANS.INVTRANS_ID
FROM INVTRANS
INNER JOIN INVOICES ON INVTRANS.INVOICE_ID = INVOICES.INVOICE_ID
LEFT JOIN BASE ON INVTRANS.MATTER_ID = BASE.MATTER_ID
GROUP BY
      base.CASENO
    , invoices.INV_NO
    , INVTRANS.TRANS_HOURS
    , INVTRANS.TRANS_AMT
    , INVTRANS.INVTRANS_ID
ORDER BY INVOICES.INV_NO, Base.CASENO

Open in new window

0
 
bjones8888PresidentAuthor Commented:
Brilliant!  That's exactly what I needed.  

Thank you.
0
 
PortletPaulfreelancerCommented:
:) good guess then (kidding)
thanks

Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.