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:

enter image description here

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

Comments

Post a Comment

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

i'm looking dynamically populate select element names of items stored array. for reason it's not working , i'm not sure why. html: <div class="col-md-4 col-md-offset-4"> <select class="select1"> </select> , <select class="select2"> </select> </div> note i'm using bootstrap layout. here's jquery i'm using try , populate ".select1" jquery: select: function() { $.each(state.greens, function(index, value) { $(".select1").append("<option value =' " + index + " '>" + state.greens[index].name + "</option>"); }); } note function 'select' stored within object called 'display'. state.greens name of array i'm trying access. so @ end of html page call display.select(); call function. no error messages showing in console. also, saw question: appending
Read more

Android soft keyboard reverts to default keyboard on orientation change -

i'm building android soft keyboard , can't seem fix bug - have arabic , qwerty keyboard , when rotate device on qwerty keyboard (or arabic shift), it's if program has "restarted" , becomes arabic keyboard without shift. the onsaveinstancestate(bundle savedinstancestate) not work because application not extend activity inputmethodservice . i put following in android manifest android:configchanges="keyboard|keyboardhidden|orientation" android:windowsoftinputmode="stateunchanged|adjustresize"> i tried using @override public void onconfigurationchanged(configuration newconfig) { super.onconfigurationchanged(newconfig); log.i(mydebug, "config changed " + currentkeyboard.equals(qwerty)); } however, currentkeyboard.equals(qwerty)) results false , made sure true before orientation change. any appreciated. i think applications fault. if application restarting on orientation change
Read more

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -

i rendering jbutton image on simulate jcheckbox behavior. why don't let jtable render jcheckbox? because need bigger checkboxes , there's no way can resize them (as far know). so, have own table cell renderer , editor. both renders jbutton , checkbox images on them. trying change image checked image unchecked image (or vice versa) , update cell value (boolean true or false). however, reason, getcelleditorvalue() not update table. so, release mouse, value goes original one. any appreciated! lot! public class tableexample extends jframe { jscrollpane pane; jpanel panel; jtable table; object[][] data; mytablemodel tm; renderer buttoncolumn; public tableexample(object[][] data) throws ioexception { string[] columns = {"column#1", "column#2", "column#3", "column#4", "column#5", "column#6"}; this.data = data; this.setpreferredsize(new dimension(700, 500));
Read more