How to calculate the difference of two SELECTs in SQL (Access, JetSQL)

Question

I need to get the difference of two SUM...WHERE queries from the same table join:

SELECT SUM(Service_template.Service_fee)
FROM (Service_template LEFT JOIN Service ON Service_template.Service_Code = Service.Service_Code) LEFT JOIN Bill ON Service.Service_ID = Bill.Service_ID
WHERE Bill.Service_ID IS NOT NULL

and

SELECT SUM(Service_template.Service_fee)
FROM (Service_template LEFT JOIN Service ON Service_template.Service_Code = Service.Service_Code) LEFT JOIN Bill ON Service.Service_ID = Bill.Service_ID

I've tried using UNION, but it returns two rows, not two columns I can make calculations on.

How do I go about doing it? I feel I am missing something trivial, so thanks in advance!

score 1 · Answer 1 · answered Apr 12 '20 at 11:45

This should work:

Select
  Sum(T1.Sum1),
  Sum(T2.Sum2),
  Sum(T1.Sum1) - Sum(T2.Sum2) As Diff1,
  Sum(T2.Sum2) - Sum(T1.Sum1) As Diff2
From
  (SELECT SUM(Service_template.Service_fee) As Sum1 FROM (Service_template LEFT JOIN Service ON Service_template.Service_Code = Service.Service_Code) LEFT JOIN Bill ON Service.Service_ID = Bill.Service_ID WHERE Bill.Service_ID IS NOT NULL) As T1,
  (SELECT SUM(Service_template.Service_fee) As Sum2 FROM (Service_template LEFT JOIN Service ON Service_template.Service_Code = Service.Service_Code) LEFT JOIN Bill ON Service.Service_ID = Bill.Service_ID) As T2

Thanks, chose @gordon-linoff 's solution – K307 Apr 12 '20 at 22:43 — K307, Apr 12 '20 at 22:43

score 1 · Accepted Answer · answered Apr 12 '20 at 12:14

If you want all three values, you can use conditional aggregation:

SELECT SUM(IIF(b.Service_ID IS NOT NULL, st.Service_fee, 0)) as total_1,
       SUM(b.Service_Id) as total_2,
       SUM(IIF(b.Service_ID IS NULL, st.Service_fee, 0)) as diff      
FROM (Service_template as st LEFT JOIN
      Service as s
      ON st.Service_Code = s.Service_Code
     ) LEFT JOIN
     Bill as b
     ON s.Service_ID = b.Service_ID;

If you just want the SUM() where Service_Id is NULL, then:

SELECT SUM(b.Service_Id) as diff      
FROM (Service_template as st LEFT JOIN
      Service as s
      ON st.Service_Code = s.Service_Code
     ) LEFT JOIN
     Bill as b
     ON s.Service_ID = b.Service_ID
WHERE b.Service_ID IS NULL;

How to calculate the difference of two SELECTs in SQL (Access, JetSQL)

2 Answers2