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
I think this can be helpfull to you:
How to SELECT based on value of another SELECT
even you can inner join tables
One option is using a
LEFT JOIN
between:then apply the
COALESCE
function on the missing Sales.The most obvious solution, although very innefficient, would be to get a list of the years from years from the same table…..
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….
(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.