SQL queries : summing two different column from different table

Question

I have two tables Received and Issued , they are related with ID field. I have tried to use the following query to get the result and it works only when both tables have values. however when one tables is empty the query is returning NULL value . I need help pls

SELECT 
    c.vaccine_id,
    c.name, 
    SUM(QRecived-IsuedQuan) Quantity 
FROM 
    vaccine c 
    LEFT OUTER JOIN (
        SELECT SUM(quantity_recieved) QRecived,vaccine_id from vaccine_detail group by vaccine_id
    ) a ON a.vaccine_id=c.vaccine_id 
    LEFT OUTER JOIN (
        select SUM(issued_quantity) IsuedQuan,vaccine_id from issued_item group by vaccine_id 
    ) b  ON b.vaccine_id=a.vaccine_id " 
GROUP BY c.vaccine_name,c.vaccine_id ";

NOTE: the above query is going to be run in SQLITE and this is just to inform you that SQLLITE does not supporting RIGHT & FULL JOINs

Maybe replacing `SUM(QRecived-IsuedQuan)` in the main query with `SUM(coalesce(QRecived, 0) - coalesce(IsuedQuan, 0))` will solve the issue already? — FrankPl, Feb 12 '14 at 13:05

score 3 · Accepted Answer · answered Feb 12 '14 at 13:13

You don't need the outer group by. The problem is that when there is no match, the values are NULL, so you need to replace them with another value:

SELECT v.vaccine_id, v.name, 
       coalesce(r.QReceived, 0) - coalesce(i.IssuedQuan, 0) as Quantity 
FROM vaccine v LEFT OUTER JOIN
     (SELECT SUM(quantity_recieved) as QReceived, vaccine_id 
      FROM vaccine_detail  
      GROUP BY vaccine_id
     ) r
     ON r.vaccine_id = v.vaccine_id LEFT OUTER JOIN
     (SELECT SUM(issued_quantity) as IssuedQuan, vaccine_id 
      FROM issued_item 
      GROUP BY vaccine_id 
     ) i
     ON i.vaccine_id = v.vaccine_id;

You can use either isnull() or coalesce(), but coalesce() is the ANSI standard function for this.

Thanks for Solution. It works Perfectly . Again Thanks for the Naming also. — Dapper Dan, Feb 13 '14 at 11:31

Tony Hopkinson · Answer 2 · 2014-02-12T22:44:01.167

0

SELECT c.vaccine_id,
       c.name,
       SUM(IfNull(QRecived,0) - IfNull(IsuedQuan,0)) Quantity
FROM vaccine c
....

Should take care of that.

edited Feb 12 '14 at 22:44

answered Feb 12 '14 at 13:04

Tony Hopkinson

20,172
3
31
39

Do sqllite support ISNULL? Read [this](http://stackoverflow.com/questions/7999608/null-substitution-in-sqlite) – Arion Feb 12 '14 at 13:06

SQL queries : summing two different column from different table

2 Answers2