Fill-in missing data in MySQL SELECT statement

Question

I want to present information from a MySQL DB (MariaDB, actually) approximately as follows:

SELECT Client, Year, SUM(Sales) 
FROM MySalesTable 
GROUP BY Client, Year 
ORDER BY Client, Year DESC

The issue is, if there is no data for a particular year for the client, MySQL entirely skips the rows, while I'd like the table to include all years in a range (say: 2015-2022) for all the clients - filled with zeros, nulls, or empty strings.

Normally, I would left-JOIN the query to a table containing bare year numbers (or filter years from a table which includes calendar information of any sort) but I do not have it at hand. I know that I can create a TEMPORARY table and populate it accordingly - but is it possible to create a transient 'pseudotable' on the fly as a subquery? Something like in the pseudocode:

SELECT ... FROM (SELECT Year FROM [2015, 2016,...2022]) LEFT JOIN ....

Thank you very much upfront.

What have you tried so far? Where are you stuck? Do you really need to do this in MySQL? Why not do this in your application? — Nico Haase, Dec 15 '22 at 11:55

score 0 · Answer 1 · answered Dec 15 '22 at 11:57

One option is using a LEFT JOIN between:

the combination of each client with every year
your query

then apply the COALESCE function on the missing Sales.

WITH combinations AS (
    SELECT clients.Client, years.Year
    FROM       (SELECT DISTINCT Client FROM MySalesTable) clients
    INNER JOIN (SELECT DISTINCT Year FROM MySalesTable) years ON 1=1
) 
SELECT Client,
       Year,
       COALESCE(total_sales, 0) AS total_sales
FROM      combinations
LEFT JOIN (SELECT Client, Year, SUM(Sales) AS total_sales
           FROM MySalesTable 
           GROUP BY Client, Year) sales
       ON combinations.Client = sales.Client 
      AND combinations.Year = sales.Year
ORDER BY Client, Year DESC

symcbean · Answer 2 · 2022-12-15T12:03:49.220

The most obvious solution, although very innefficient, would be to get a list of the years from years from the same table.....

SELECT a.client, allyears.year, sum(a.sales) 
FROM mysalestable
FROM (SELECT DISTINCT year FROM MySalesTable) allyears 
LEFT JOIN MySalesTable a
ON allyears.year=a.year
....

However this will still not list clients with no sales in a year - really you need a data source producing a list of all years and all clients - that needs a cartesian join....

SELECT allclients.client, allyears.year, sum(a.sales) 
FROM (SELECT DISTINCT year FROM MySalesTable) allyears
JOIN (SELECT DISTINCT client FROM MySalesTable) allclients 
LEFT JOIN MySalesTable a
ON allyears.year=a.year
AND allclients.client=a.client
GROUP BY allclients.client, allyears.year
ORDER BY allclients.client, allyears.year DESC

(You may have other tables which are less expensive to query for allyears and all clients)

Unfortunately MySQL does not have a mechanism for implementing a row generator.

score -1 · Answer 3 · answered Dec 15 '22 at 11:51

I think this can be helpfull to you:

How to SELECT based on value of another SELECT

select t1.*, t2.* from (
SELECT Client, Year, SUM(Sales) FROM MySalesTable GROUP BY Client, Year
) as t1,
(
SELECT Client, Year, SUM(Sales) FROM MySalesTable GROUP BY Client, Year
) as t2
where t1.Client = t2.Client

even you can inner join tables

Fill-in missing data in MySQL SELECT statement

3 Answers3