Thoughts on how to replace UNION clause in MariaDB statement

Question

(Code to create the test tables is at the bottom of this message.)

Given two tables:

SELECT * FROM test_users;
+----+-------+
| ID | name  |
+----+-------+
|  1 | Tom   |
|  2 | Wendy |
|  3 | Fred  |
|  4 | Sandy |
+----+-------+
4 rows in set (0.000 sec)

SELECT * FROM test_hours;
+----+----------+------+-------+
| ID | users_ID | year | hours |
+----+----------+------+-------+
|  1 |        1 | 2018 |     3 |
|  2 |        1 | 2018 |     5 |
|  3 |        1 | 2019 |     7 |
|  4 |        1 | 2019 |     2 |
|  5 |        1 | 2019 |     9 |
|  6 |        1 | 2020 |     5 |
|  7 |        1 | 2020 |     9 |
|  8 |        2 | 2018 |     7 |
|  9 |        2 | 2018 |    11 |
| 10 |        2 | 2018 |     8 |
| 11 |        2 | 2019 |    10 |
| 12 |        2 | 2019 |    12 |
| 13 |        3 | 2018 |     4 |
| 14 |        3 | 2018 |     1 |
| 15 |        3 | 2018 |    15 |
| 16 |        3 | 2020 |    10 |
| 17 |        3 | 2020 |    12 |
| 18 |        4 | 2019 |     7 |
| 19 |        4 | 2019 |    11 |
| 20 |        4 | 2020 |     4 |
| 21 |        4 | 2020 |     6 |
+----+----------+------+-------+
21 rows in set (0.000 sec)

I can use a pretty simple join to get a summary of hours by user and year:

SELECT name, year, SUM(hours) 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
GROUP BY users_ID, year;
+-------+------+------------+
| name  | year | SUM(hours) |
+-------+------+------------+
| Tom   | 2018 |          8 |
| Tom   | 2019 |         18 |
| Tom   | 2020 |         14 |
| Wendy | 2018 |         26 |
| Wendy | 2019 |         22 |
| Fred  | 2018 |         20 |
| Fred  | 2020 |         22 |
| Sandy | 2019 |         18 |
| Sandy | 2020 |         10 |
+-------+------+------------+
9 rows in set (0.001 sec)

If I want only one year, I can do this:

SELECT name, year, SUM(hours) 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
WHERE year = 2020
GROUP BY users_ID, year;
+-------+------+------------+
| name  | year | SUM(hours) |
+-------+------+------------+
| Tom   | 2020 |         14 |
| Fred  | 2020 |         22 |
| Sandy | 2020 |         10 |
+-------+------+------------+
3 rows in set (0.000 sec)

Wendy drops out because she doesn't have any 2020 hours. What I really want, though, is:

+--------+------+------------+
| name   | year | SUM(hours) |
+--------+------+------------+
| Tom    | 2020 |         14 |
| Wendy  | 2020 |          0 |
| Fred   | 2020 |         22 |
| Sandy  | 2020 |         10 |
+--------+------+------------+

I can do it with a UNION clause:

SELECT name, year, SUM(hours) 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
WHERE year = 2020
GROUP BY users_ID, year

UNION

SELECT DISTINCT name, 2020, 0 
FROM test_hours 
JOIN test_users
  ON users_ID = test_users.ID
WHERE NOT EXISTS (
    SELECT *
    FROM test_hours
    WHERE users_ID = test_users.ID AND year = 2020);
+-------+------+------------+
| name  | year | SUM(hours) |
+-------+------+------------+
| Tom   | 2020 |         14 |
| Fred  | 2020 |         22 |
| Sandy | 2020 |         10 |
| Wendy | 2020 |          0 |
+-------+------+------------+
4 rows in set (0.001 sec)

But I'm wondering if there is a better way; the complete SQL statement already contains many UNION clauses and I'm trying to wrap my head around how to eliminate them.

I can't figure out how to do this one.

Any ideas?

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

DROP TABLE IF EXISTS test_hours;
CREATE TABLE test_hours (
  ID int(10) AUTO_INCREMENT PRIMARY KEY,
  users_ID int(10),
  year int(4),
  hours int(4)
);

DROP TABLE IF EXISTS test_users;
CREATE TABLE test_users (
  ID int(10),
  name varchar(60) 
);

INSERT INTO test_users (ID, name) VALUES
  (1, 'Tom'), (2, 'Wendy'), (3, 'Fred'), (4, 'Sandy');

INSERT INTO test_hours (users_ID, year, hours) VALUES
  (1, 2018, 3), (1, 2018, 5), (1, 2019, 7), (1, 2019, 2), (1, 2019, 9), (1, 2020, 5), (1, 2020, 9),
  (2, 2018, 7), (2, 2018, 11), (2, 2018, 8), (2, 2019, 10), (2, 2019, 12),
  (3, 2018, 4), (3, 2018, 1), (3, 2018, 15), (3, 2020, 10), (3, 2020, 12),
  (4, 2019, 7), (4, 2019, 11), (4, 2020, 4), (4, 2020, 6);

user_1144035 · Answer

Probably the simplest method is to use conditional aggregation:

SELECT u.name, 2020 as year,
       SUM(CASE WHEN h.year = 2020 THEN h.hours ELSE 0 END) 
FROM test_hours h JOIN
     test_users u
     ON h.users_ID = u.ID
GROUP BY u.ID;

Note that I changed the GROUP BY to use the test_users table where ID is presumably the primary key -- so it is okay to select another column such as name from the same table.

Another method would use a LEFT JOIN:

SELECT u.name, 2020 as year,
       SUM(h.hours)
FROM test_users u LEFT JOIN
     test_hours h
     ON h.users_ID = u.ID AND
        h.year = 2020
GROUP BY u.id, u.name;

user_1766831 · Answer

You have an overly complex example of a common problem -- wanting a list of things even where certain years (or days or hours) are missing from the dataset (and you want to display zero).

The general solution is to start with a table of all the possible values. This probably needs to be computed independently of the data. MariaDB has an especially handy way of doing such --

SELECT num AS 'year'
    FROM seq_2000_to_2100  -- so you don't have to change this
    WHERE num BETWEEN 2018 AND 2020  -- unclear on what range you want
    -- You could do MIN(year) to MAX(year) from test_hours

Then use that as follows:

SELECT u.name,
       year,
       COALESCE(SUM(h.hours), 0) AS tot_hours  -- Note COALESCE to avoid NULL
    FROM ( ( the above query )
           LEFT JOIN test_hours USING(year)  -- note LEFT
         )
    JOIN users ...
    WHERE ...

Thoughts on how to replace UNION clause in MariaDB statement

2 Answers