skip to Main Content

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.

3

Answers


  1. 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

    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
  3. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search