Query Mysql - Return 0 for values that do not exist in both tables

Question

I have one problem with one query in mysql. I have 2 tables: table A and table B

Table A:

Name - Total

France | 20
Spain | 10
England | 5
Italy | 10
France | 5

Table B

Name

France
Spain
Italy
France
Greece

My objective is obtain the sum of the total variable for each of the values in table B, ie:

France | 25
Spain | 10
Italy | 10
France | 5
Greece | 0

My problem is i can't it returns 0 when the value in table B not exist in table A.

In this moment I have this query:

select B.name, SUM(A.total) 
from table A , table B
where A.name = B.name
group by B.name
having count(B.name)>1

Answer 1

I can't see from your question why you need COUNT(b.name) > 1. However both pieces of information may be recovered thus:

-- Test data
CREATE TABLE a ( name varchar(20), total integer );
CREATE TABLE b ( name varchar(20) );

INSERT INTO a VALUES ( 'France', 20 ), ( 'Spain', 10 ),
( 'England', 5 ), ( 'Italy', 10 ), ( 'France', 5 );

INSERT INTO b VALUES ( 'France' ), ( 'Spain' ), ( 'Italy' ), ( 'France' ), ( 'Greece' );


    SELECT c.name,
    COALESCE(d.number, 0) AS rows,
    COALESCE(e.total, 0) AS total
FROM 
   ( SELECT DISTINCT name FROM a
     UNION
     SELECT DISTINCT name FROM b
     ) AS c
LEFT JOIN
   (  SELECT name, COUNT(1) AS number FROM b GROUP BY name ) AS d
   ON (c.name = d.name)
LEFT JOIN
   (  SELECT name, SUM(total) AS total FROM a GROUP BY name ) AS e
   ON (c.name = e.name);

This is very inefficient but it does the job. If you don't actually need information such as the number of b rows, then the query may be greatly simplified.

The result of above is,

name    rows   total
France  2       25
Spain   1       10
England 0        5
Italy   1       10
Greece  1        0

Note that "rows" refers to the rows of the b table. So you can have 0 rows, as is the case of England. It would be easier to JOIN the tables if the condition was that rows was one or more (in your question you specified > 1, so two or more).

To simply

My objective is obtain the sum of the total variable for each of the values in table B

you can first select the unique country names from B, then LEFT JOIN against the totals of table A. To work around the NULL values for missing countries you use COALESCE.

SELECT c.name, COALESCE(d.total, 0) AS total
FROM 
   ( SELECT DISTINCT name FROM b ) AS c
LEFT JOIN
   ( SELECT name, SUM(total) AS total FROM a GROUP BY name ) AS d
ON (c.name = d.name)

giving

France  25
Spain   10
Italy   10
Greece  0

Or:

SELECT c.name, SUM(COALESCE(a.total, 0)) AS total
FROM 
   ( SELECT DISTINCT name FROM b ) AS c
LEFT JOIN
   a 
ON (c.name = a.name)
GROUP BY c.name

If you can have unique country names in B, you can replace ( SELECT DISTINCT name FROM b ) AS c with b (of course then the cs in the query also become b's).

Answer 2

To return rows from table B that have no matching rows in table A, you could use an "outer" join operation.

Firstly, ditch the old-school comma syntax for the join operation, and use the JOIN keyword instead. To specify an "outer" join use the keyword "LEFT" before the "JOIN" keyword, put the driving table (table B, the table you want to return all of the rows from, on the left side of the JOIN. And move the comparison predicate that performs the "match" between the two tables from the WHERE clause to the ON clause.

In the example data, I notice that are "duplicate" name values in table B (e.g. 'France') and the resultset you show contains these two rows:

France | 25
France | 5

We can see how that first row would be produced, the SUM of the values from two 'France' rows in table A, but that second row, that's a bit of a pickle to figure out. (Maybe that's what the HAVING clause in your query was an attempt to deal with?)

So, to simplify things, I'm going move forward with the assumption that the second France row in the example output isn't actually needed in the result

Based on the example data, and the specified result (except for the second France 5 row), we could do something like this, using an inline view to get a distinct list of name from B.

SELECT d.name
     , IFNULL(SUM(a.total),0) AS total
  FROM ( SELECT b.name
           FROM B b
          GROUP BY b.name
       ) d
  LEFT
  JOIN A a
    ON a.name = d.name
 GROUP BY d.name

---

If table B doesn't actually have duplicate values for name, and instead has 'England' in place of that second 'France' row, and the result should include England | 5 rather than France | 5...

If we have a guarantee that name column is UNIQUE in table B, we can eliminate the inline view, and just do this:

SELECT b.name
     , IFNULL(SUM(a.total),0) AS total
  FROM B b
  LEFT
  JOIN A a
    ON a.name = b.name
 GROUP BY b.name

---

The query above is the normative pattern for returning a result like the one you describe, but it isn't the only pattern. There are couple of other forms that will also return an equivalent result.

As a demonstration of a different approach, one which doesn't use a join operation:

SELECT b.name
     , IFNULL(SELECT SUM(a.total) FROM A a WHERE a.name = b.name) AS total
  FROM B b

This approach usually doesn't perform as well as the join for large numbers of rows returned from B. (With small sets, we don't notice a difference, it's with larger sets that the performance difference can become noticeable.)