I have the 3 tables below with the following data
create table model ( id int comment 'The model id. Primary Key', name varchar(50) comment 'Models name', price int comment 'The price of the model', primary key (id) ); create table country ( id int comment 'The country id. Primary Key', name varchar(50) comment 'Countrys name', primary key (id) ); create table sales ( model_id int comment 'Models id', country_id int comment 'Countrys id', quantity int comment 'Number of units sold', sales_date date comment 'Date of the sale' ); INSERT INTO COUNTRY VALUES (1, 'Brazil'), (2, 'Italy'), (3, 'China'); INSERT INTO model VALUES (1, 'ZTX100', 100), (2, 'AHT567', 200), (3, 'JPF090', 300); INSERT INTO sales VALUES (1, '1', 500, '2017-01-10'), (2, '2', 600, '2017-05-11'), (3, '3', 700, '2017-07-21'), (1, '3', 200, '2019-08-14');
I need to write a query that calculates country-wise sales for all of the models along with the revenue generated for the year 2017. The result should be in the following format: country_name | model_name | revenue
I’m trying this query below. I need to bring the country/models that do not have any sales too with a revenue of 0, but my query only return the country/models that have any sales.
SELECT c.name as country_name, m.name as model_name, IFNULL(SUM(s.quantity * m.price),0) as revenue FROM sales s right join model m on s.model_id = m.id right join country c on s.country_id = c.id where date(s.sales_date) between '2017-01-01' and '2017-12-31' group by c.id, m.id order by c.id, m.id;
Result of the query
Brazil ZTX100 50000 Italy AHT567 120000 China JPF090 210000
What i really need (the order is not important)
Brazil ZTX100 50000 Brazil AHT567 0 Brazil JPF090 0 Italy ZTX100 0 Italy AHT567 120000 Italy JPF090 0 China ZTX100 0 China AHT567 0 China JPF090 210000
There is a simple way to do that?
Advertisement
Answer
You can cross join
the countries and model table to generate all possible combinations, and then bring in the sales with a left join
:
SELECT c.name as country_name, m.name as model_name, IFNULL(SUM(s.quantity * m.price),0) as revenue FROM country c cross join model m left join sales s on s.model_id = m.id and s.country_id = c.id and s.sales_date >= '2017-01-01' and s.sales_date < '2018-01-01' group by c.id, m.id, c.name, m.name order by c.id, m.id;
Note that I did the following changes in the query:
use half-open intervals for the date comparison; this will be more efficient, since no date function needs to be applied on
sales_date
add all non-aggregated columns to the
group by
clause, making the query compliant with MySQL modeONLY_FULL_GROUP_BY
, which is enabled by default starting MySQ 5.7