sql - how to join two tables together with condition and sum them up? -

i had sql command display income report product contain sum of vat , non-vat , gross total in specific year. want report show income year year. i've tried modified existing sql command when executed it, result incorrect. kindly me check this? limited skill of sql, stuck here months.

my tables below

tbl_bill_total

bill_id | bill_total | cust_id | shown date   1     |   500      |   12    |  6/6/12   2     |   500      |   14    |  8/8/12   3     |   1000     |   13    |  10/11/12   4     |   1000     |   12    |  12/10/13   5     |   1200     |   13    |  1/11/13   6     |   500      |   12    |  3/11/13 

tbl_vat_bill_total

vat_id  |  vat_total |  if_paid| showndate | cust_id    1     |    100     |   false |  1/6/12   |  10   2     |    200     |   true  |  2/6/12   |  11   3     |    100     |   true  |  7/8/12   |  12   1     |    400     |   false |  13/10/13 |  14   2     |    500     |   true  |  14/11/13 |  12   3     |    100     |   false |  15/11/13 |  11   4     |    200     |   true  |  20/11/13 |  12 

the expected result should this

bill_total  |  vat_total | sum_of_all | month  | year   500       |   200      |   700      |   6    |  2012   1000      |   100      |   1100     |   8    |  2012   1000      |   0        |   1000     |  11    |  2012          1000      |    0       |   1000     |  10    |  2013   1700      |    700     |   2400     |  11    |  2013 

here sql command didn't work…

select      sum(tbt.bill_total) bill_total_     ,iif(sum_vat_total null, 0, sum_vat_total) vat_total_     ,iif(sum_vat_total null, 0, sum_vat_total) + sum(bill_total) sum_of_all     ,month(showndate) month     ,year(showndate) yearall tbl_bill tbt left join (     select          sum(vat_total) sum_vat_total         ,month(showndate) month         ,year(showndate) yearall     tbl_vat     if_paid = true      group          month(showndate)         ,year(showndate)     ) tvt         on tvt.month = month(tbt.showndate)   group      month(showndate)     ,sum_vat_total     ,year(showndate) 

so here solution problem. sql should work in ms access tested sql server because there no access available :(. if_paid varchar in version, should remove quotes.

select  sum(bill_total) bill_total_,  sum(vat_total) vat_total_, sum(bill_total) + sum(vat_total) sum_of_all, month, yearall    (select    bill_total, 0 vat_total, month(showndate) month, year(showndate) yearall   tbl_bill_total   union   select    0 bill_total, vat_total, month(showndate) month, year(showndate) yearall   tbl_vat_bill_total if_paid='true') data group month, yearall 

in inner select collect bill_total , vat_total values. here no grouping. happens in outer select month , yearall.

with got wished result, except 2012/08 think got typo here bill_total should here 500.

this sql build test data:

create table tbl_bill_total     (bill_id int, bill_total int, cust_id int, showndate datetime) ;  insert tbl_bill_total     (bill_id, bill_total, cust_id, showndate) values     (1, 500, 12, '2012-06-06 02:00:00'),     (2, 500, 14, '2012-08-08 02:00:00'),     (3, 1000, 13, '2012-10-11 02:00:00'),     (4, 1000, 12, '2013-12-10 01:00:00'),     (5, 1200, 13, '2013-01-11 01:00:00'),     (6, 500, 12, '2013-03-11 01:00:00') ;    create table tbl_vat_bill_total     (vat_id int, vat_total int, if_paid varchar(5), showndate datetime, cust_id int) ;  insert tbl_vat_bill_total     (vat_id, vat_total, if_paid, showndate, cust_id) values     (1, 100, 'false', '2012-01-06 01:00:00', 10),     (2, 200, 'true', '2012-02-06 01:00:00', 11),     (3, 100, 'true', '2012-07-08 02:00:00', 12),     (1, 400, 'false', '2013-13-10 01:00:00', 14),     (2, 500, 'true', '2013-14-11 01:00:00', 12),     (3, 100, 'false', '2013-15-11 01:00:00', 11),     (4, 200, 'true', '2013-20-11 02:00:00', 12) ;