bjones8888

asked on

# 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:

Here's the result I get from my data:

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?

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
```

Here's the result I get from my data:

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?

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

:) good guess then

thanks

Cheers, Paul

*(kidding*)thanks

Cheers, Paul

ASKER

Thank you.