Mysql: Subtracting values based on two queries -

-

i have following 2 queries.

select      account_name,sum(amount) amount1       entries  left join      accounts on accounts.id = entries.accounts_id      side = 'd' , op_balance_dc = 'd' group      accounts.id

here's result of query:

query1

select      account_name,sum(amount) amount2       entries  left join      accounts on accounts.id = entries.accounts_id      side = 'c' , op_balance_dc = 'd' group      accounts.id

here's result of second query

query2

i not trying display results of above 2 queries, trying achieve account_name, amount1 , amount2 above queries , subtract amount2 amount1- , add value table's column.

example:

(amount1-amount2)+op_balance  //here op_balance column name

and display account_name , (amount1-amount2)+op_balance

could please me query?

thanks :)

please let me know if need anymore information.:)

edited

here's structure of tables:

thanks :)

edit2

table name: entries (case-1)

  id    trans_id   accounts_id    amount    side    1      1       12               1000      d    2      1        1               1000      c    7      4        1              14000      d    8      4        2              14000      c

table name: entries (case-2)

  id    trans_id   accounts_id  amount        side   1           1       12          1000       d   2           1        1          1000       c

your code work case-1 not case-2. used lentries.accounts_id = entries.accounts_id in code , why since there's no multiple values same accounts_id in table join , code not counting values in case-2.

but trying data. :)

here edited query accounts

select   a.id,   a.account_name,   a.op_balance,   ifnull(e.amount1,0) amount1,   ifnull(l.amount2,0) amount2,   ((ifnull(sum(e.amount1),0)-ifnull(l.amount2,0))+a.op_balance) balance accounts   left join (select                accounts_id,                sum(amount)     amount1              entries                left join accounts                  on accounts.id = entries.accounts_id              entries.side = 'd'                  , accounts.op_balance_dc = 'd'              group accounts.id) e     on e.accounts_id = a.id   left join (select                accounts_id,                sum(amount)     amount2              entries                left join accounts                  on accounts.id = entries.accounts_id              side = 'c'                  , op_balance_dc = 'd'              group accounts.id) l     on l.accounts_id = a.id group a.id

Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more