I have invoices:

Invoice No: 10001
Discount: $30
ItemA, 2 Pcs, total $100 => net $100-$10 = $90
ItemB, 2 Pcs, total $200 => net $200-$10 = $190
ItemC, 2 Pcs, total $300 => net $300-$10 = $290
because $30 discount is distributed evenly to each item, regarding the item value and qty

Invoice No: 10002
Discount: $40
ItemA, 2 Pcs, total $100 => net $100-$20 = $80
ItemB, 2 Pcs, total $200 => net $200-$20 = $180
each item gets $20 discount, regarding the item value and qty

I expect the report as:

Group by item:
ItemA, 4 Pcs, total $170 (90+80)
ItemB, 4 Pcs, total $370 (190+180)
ItemC, 2 Pcs, total $290 (290)

so instead of this query:

select cItem,sum(nQty),sum(nAmount) from invoice group by cItem

I want to do:

select cItem,sum(nQty),sum(nAmount-nDisc/nrecord) from invoice group by cItem

where nrecord is:

select count(*) as nrecord from invoice group by cinvoice

Is that possible to do it in one query?

Thanks for the help.

Regards,

Jerry Yang