mysql - How do I sum the child table? -


i've tried query, got wrong result... looked @ many references not find answer.

select a.id, sum(b1.amount) section1, sum(abs(b2.amount)) section2 parent_table inner join child_table b1 on a.id=b1.parent_id inner join child_table b2 on a.id=b2.parent_id b1.bh_status in ('section1') , b2.bh_status in ('section2') group a.id 

tables are:

parent_table

id  | customer_namem | date_register ----+----------------+-------------- 1   | customer1      | 2014-20-11    2   | customer2      | 2014-21-11    3   | customer3      | 2014-22-11    4   | customer4      | 2014-23-11    5   | customer5      | 2014-23-11    

child_table

id  | ch_key    | ch_value | parent_id  ----+-----------+----------+---------- 1   | deposit   | 100      | 1 2   | deposit   | 500      | 1 3   | withdraw  | 100      | 1 4   | withdraw  | 100      | 1 5   | deposit   | 10       | 2 6   | deposit   | 100      | 2 7   | deposit   | 50       | 3 8   | deposit   | 50       | 3 9   | withdraw  | 10       | 3 10  | deposit   | 50       | 4 11  | withdraw  | 50       | 4 12  | withdraw  | 150      | 5 

i want display this:

id  | customer_namem | deposit | withdtaw | date_register 1   | customer1      | 600     | 200      | 2014-20-11   2   | customer2      | 110     | 0        | 2014-20-11   3   | customer3      | 100     | 10       | 2014-20-11   4   | customer4      | 50      | 50       | 2014-20-11   5   | customer5      | 0       | 150      | 2014-20-11   

you have use case, this:

select    a.id, a.customer_namem,    sum(case when ch_key = "deposit" ch_value else 0 end) deposit,   sum(case when ch_key = "withdraw" ch_value else 0 end) withdraw,   a.date_register parent_table inner join child_table b on a.id=b.parent_id group a.id 

working demo: http://sqlfiddle.com/#!2/01ad95/1


Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -