Simple and beautiful.
I didn't know that can be done this way.
Thank you

Jerry



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