SUM per GROUP

i have this problem

i have
Trans table
@TransID | Date | DiscountPercent | DiscountCurrency
tr-1 | 1/1/2005 | 0 | 1000

Trans Details table
@TransID | ProductID | QtyBuy | SellPrice |
tr-1 | prod1 | 10 | 100
tr-1 | prod2 | 10 | 10
tr-1 | prod3 | 15 | 100

if DiscountCurrency is > 0 then
TotalDiscount = DiscountCurrency
else
TotalDiscount = (qty * SellPrice for tr-1) * DiscountPercent/100
endif

question :
the report want to show per GROUP TransID
TransID | TotalDiscount | Total (TotalDiscount - (qtyBuy*SellPrice for Each TransID)) -> how to do something like this??????

thanks

hmmmm

Quote:

---

Trans table
@TransID | Date | DiscountPercent | DiscountCurrency
tr-1 | 1/1/2005 | 0 | 1000

Trans Details table
@TransID | ProductID | QtyBuy | SellPrice |
tr-1 | prod1 | 10 | 100
tr-1 | prod2 | 10 | 10
tr-1 | prod3 | 15 | 100

if DiscountCurrency is > 0 then
TotalDiscount = DiscountCurrency
else
TotalDiscount = (qty * SellPrice for tr-1) * DiscountPercent/100
endif

---

No idea if this will work right - but worth a try/tweak?

Code:

---

SELECT
  [qrySubDisc].[transid],
  Sum([qrySubDisc].[TotalDisc]) as [TotalDiscount],
  Sum([qrySubDisc].[TotalSold])-Sum([qrySubDisc].[TotalDisc]) as [TheTotal]
FROM
(
SELECT
  [tbltrans].[transid],
  [tbltransdetail].[sellprice]*[tbltransdetail].[qtybuy] as TotalSold,
  iif([tbltrans].[discountcurrency]>0,[tbltrans].[discountcurrency],([tbltransdetail].[sellprice]*[tbltransdetail].[qtybuy])*([tbltrans].[discountpercent]/100)) as TotalDisc
FROM
  [tbltrans] left join [tbltransdetail] on [tbltrans].[transid]=[tbltransdetail].[transid]
) as qrySubDisc
GROUP BY
  [qrySubDisc].[transid]

---

The sub query gets the totals for sold (no discount) and the total discount per record. Then this is grouped in the outer Select statement, which if this runs right, means that you can join to the original table for other info, and is grouped on the TransId...

Post up if it worked?? pls?

Note: Wrote from my head - probably wrong...

Ecniv i'm not sure how to do the SELECT query at CR..but i'll try those query at my dbase and post what happened

i can make it work now using Formula at CR..but can't explain how to do it since that's at my home..i'll post at Monday ok..

thank you very much Ecniv

sorry for late reply because it's i'm kinda busy and always forgot to copy and try for my project

Ecniv u're awesome :thumb: ..i copy paste the query and it's working without any tweaking except for changing field name and it gave me the same result from what i did at CR..

o man..i never realized that u can do 'IIF' at query

and what i do at CR is something like this
i made a Formula Field call TotalDiscount

Code:

---

if {tblSale.CutPrice} > 0 then
  {tblSale.CutPrice}
else  {tblSale.Disc}/100*{tblSaleDetails.SalePrice}*{tblSaleDetails.SaleQuantity}

---

and put it at SaleID Group Footer..i suppress the Details Section coz it's not needed

i got other Formula call Total

Code:

---

({tblSaleDetails.SalePrice}*{tblSaleDetails.SaleQuantity}) - {@TotalDiscount}

---

put it at Detail Section..and SUM it for SaleID GROUP
and it display the correct value..i'm now this is doing the 'hard way' but at least it works for me :)

thanks Ecniv..i'm learning something new