2 Sums from 3 tables MS Access SQL query

Question

I want help on this. I'm using MS Access DB

TIA

I want to combine these 2 SQL queries to get a single output.

select a.salesid, salesdate, customername, sum(qty*price)  
from tblsales a 
inner join tblsales_details b on b.salesid = a.salesid 
where customername like '%arvin%' 
group by a.salesid, salesdate, customername

Result:

001 1/1/1970 arvin 109500

Query #2:

select a.salesid, sum(b.pamount) as payment 
from tblsales a 
inner join tblpayments b on b.salesid = a.salesid 
where customername like '%arvin%' 
group by a.salesid

Result:

001 105000

I want the result will be like this

001 1/1/1970 arvin 109500 105000

TIA

Nishant Gupta · Accepted Answer · 2018-04-13T07:23:41.643

0

Try this solution:

SELECT x.salesid, x.salesdate, x.customername, x.amount, y.payment
FROM (
      select a.salesid, salesdate, customername, sum(qty*price) AS amount  
      from tblsales a 
      inner join tblsales_details b 
      on b.salesid = a.salesid 
      where customername like '%arvin%' 
      group by a.salesid, salesdate, customername
     ) AS x
INNER JOIN (
            select a.salesid, sum(b.pamount) as payment 
            from tblsales a 
            inner join tblpayments b 
            on b.salesid = a.salesid 
            where customername like '%arvin%' 
            group by a.salesid
           ) AS y
ON x.salesid = y.salesid

If there is no pamount for an id. Then use LEFT JOIN like this in Second SubQuery:

select a.salesid, sum(IFF(b.pamount IS NULL, 0, b.pamount)) as payment 
from tblsales a 
LEFT JOIN tblpayments b 
on b.salesid = a.salesid 
where customername like '%arvin%' 
group by a.salesid

edited Apr 13 '18 at 07:23

answered Apr 13 '18 at 03:55

Nishant Gupta

3,533
1
11
18

hi, thank you. This is what im looking for. thank you very much. – JuanMan Apr 13 '18 at 04:31
Welcome... @JuanMan – Nishant Gupta Apr 13 '18 at 04:32
hi, i have another problem, what if no record found on payment, how to return a 0 value? – JuanMan Apr 13 '18 at 07:03
You have to make a change in payment subquery. – Nishant Gupta Apr 13 '18 at 07:16

Yogesh Sharma · Answer 2 · 2018-04-13T07:23:52.210

I think you would just need to add other joins with table tblpayments

select a.salesid, a.salesdate, a.customername, 
       sum(b.qty*b.price) as TotalPrice, sum(p.pamount) as payment 
from tblsales a 
inner join tblsales_details b on b.salesid = a.salesid 
inner join tblpayments p on p.salesid = a.salesid -- you might be need left join
where a.customername like '%arvin%' 
group by a.salesid, a.salesdate, a.customername

I suspect that the above would not give what you want, but the second version might helpful by using a subquery

select a.salesid, a.salesdate, a.customername, sum(b.qty*b.price) as TotalPrice, 
      (select IIF(SUM(pamount) IS NULL, 0, SUM(pamount)) from tblpayments where salesid  = a.salesid) as payment 
from tblsales a 
inner join tblsales_details b on b.salesid = a.salesid 
where a.customername like '%arvin%' 
group by a.salesid, a.salesdate, a.customername

2 Sums from 3 tables MS Access SQL query

2 Answers2