sql - Need all Sales Reps on one Line -

below have following sql statement:

with salesdetail ( select postar.txdate  ,postar.accountlink  ,postst.accountlink stklnk  ,postst.quantity qtysold  ,postst.cauditnumber  ,(postst.credit-postst.debit)-(postst.quantity*postst.cost) profit  ,(((postst.credit-postst.debit)-(postst.quantity*postst.cost)))/((((postst.credit-postst.debit))))*100 grossprofitpercent  ,(postst.quantity*postst.cost) cost  ,(postst.credit-postst.debit) totsales  ,(((postst.credit-postst.debit)-(postst.quantity*postst.cost))) / ((((postst.quantity*postst.cost)+(0.00000000000001))))*100 markuppercent  ,concat(stkitem.code, ' - ' ,stkitem.description_1) stkitemcode  ,salesrep.code repcode  ,salesrep.name repname  ,client.account custid  ,client.name custname   ,case           when ((((postst.credit+postst.debit)-(postst.cost*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) > 0 , ((((postst.credit+postst.debit)-((postst.cost+0.0000001)*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) < 25  2          when ((((postst.credit+postst.debit)-(postst.cost*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) >= 25 , ((((postst.credit+postst.debit)-((postst.cost+0.0000001)*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) < 35  2.5          when ((((postst.credit+postst.debit)-(postst.cost*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) >= 35 , ((((postst.credit+postst.debit)-((postst.cost+0.0000001)*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) < 45  3          when ((((postst.credit+postst.debit)-(postst.cost*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) >= 45 , ((((postst.credit+postst.debit)-((postst.cost+0.0000001)*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) < 55  3.5          when ((((postst.credit+postst.debit)-(postst.cost*postst.quantity))/((postst.cost+0.0000001)*postst.quantity))*100) >= 55 4          else 0 end commpayablepercent  postar   inner join postst on postst.cauditnumber = postar.cauditnumber  inner join stkitem on stkitem.stocklink = postst.accountlink  inner join salesrep on salesrep.idsalesrep = postar.repid  inner join client on client.dclink = postar.accountlink  )     select  salesdetail.repname     ,   (((salesdetail.commpayablepercent)/100)) * ((salesdetail.totsales))      reptotcomm     ,   sum(salesdetail.totsales) totalsales      salesdetail    group salesdetail.repname, salesdetail.totsales,       salesdetail.commpayablepercent    order salesdetail.repname 

i following result:

as can see, multipling totsales determained case formulala. fine. however, not want reps on each line. want rep name , total commission payable.

eg:

gray meiring - $5000 - $6000

and not:

 gray meiring  gray meiring   gray meiring etc etc...  

is issue coming sum function or perhaps group function?

many thanks! :)

you should use group salesdetail.repname only, achieve it, can add subquery, try that:

................ select  repname,         sum(reptotcomm),         sum(totalsales) (     select  salesdetail.repname     ,   (((salesdetail.commpayablepercent)/100)) * ((salesdetail.totsales))      reptotcomm     ,   sum(salesdetail.totsales) totalsales      salesdetail    group salesdetail.repname, salesdetail.totsales, salesdetail.commpayablepercent            ) x group repname order repname