>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

While not thinking it through, you can do something like

SELECT;
  Cur1.cItem,Cur1.nQty,Cur1.nAmount-Cur1.nDisc/Cur1.nrecord;
FROM (;
 select cItem,sum(nQty) as nQty,sum(nAmount) as nAmount, SUM(nDisc) as nDisc ,count(*) as nrecord from invoice group by cItem) AS Cur1

Note that the last field is not named and the calculation is nAmount-(nDisc/nrecord)

So you do the grouping first and then the maths

Update: there was a mixup of record and field