Adding some cumulative values and total rows from two or more tables

Question

I have a relating selecting data from two tables:

---

 CREATE TABLE IF NOT EXISTS football (
       id integer NOT NULL,
       name varchar(50) NOT NULL DEFAULT '',
       city varchar(100) NOT NULL DEFAULT ''
   )

Dumping data for table football

INSERT INTO football (id, name, city) VALUES
    (1, 'Real Madrid', 'Madrid'),
    (2, 'Atletico Madrid', 'Madrid'),
    (3, 'AS Rome', 'Rome'),
    (4, 'Lazio Rome', 'Rome'),
    (5, 'FC Barcelona', 'Barcelona'),
    (6, 'Steaua Bucharest', 'Bucharest');

---

CREATE TABLE IF NOT EXISTS handball (
    id integer NOT NULL,
    name varchar(50) NOT NULL DEFAULT '',
    city varchar(100) NOT NULL DEFAULT ''
)

Dumping data for table handball

INSERT INTO handball (id, name, city) VALUES
    (1, 'Getafe Madrid', 'Madrid'),
    (2, 'FC Koln', 'Koln'),
    (3, 'Dinamo Bucharest', 'Bucharest'),
    (4, 'SCM Bucharest', 'Bucharest');

I need a selecting from the database and a result like:

City|Cumulative number of football club from this city|Cumulativenumber of handball club from this city|Total number of football and handball clubs
Madrid|2|1|3
Rome|2|0|2
Barcelona|1|0|1
Bucharest|1|1|2
Koln|0|1|1

Answer 1

The right solution for such query is FULL OUTER JOIN. Unfortunately MySQL doesn't implement FULL OUTER JOIN.

You may simulate it using UNION. This is a simple solution for your question:

SELECT
    city
  , sum(football_count) AS football_count
  , sum(handball_count) AS handball_count
  , sum(total_count) AS total_count
FROM (
      SELECT city, 1 AS football_count, 0 AS handball_count, 1 AS total_count
      FROM football
      UNION ALL
      SELECT city, 0 AS football_count, 1 AS handball_count, 1 AS total_count
      FROM handball
) AS all_sports
GROUP BY city;

---

There is solution for PostgreSQL using FULL OUTER JOIN (you tagged your question with tag 'postgresql')

SELECT 
      COALESCE(football.city, handball.city) AS city
    , COUNT( football.id ) AS football_count
    , COUNT( handball.id ) AS handball_count
    , COUNT( football.id ) + COUNT( handball.id ) AS total_count
FROM football
    fULL OUTER JOIN handball ON handball.city = football.city
GROUP BY 1