CODEWITHSUNDEEP
mysqlsqldatejoin
Steven H
Steven H

Reputation: 33

missing row if sales did not occur in one year vs other

Given below is the table definition and data:

CREATE TABLE bike
(
  id INTEGER,
  name VARCHAR(50),
  price INTEGER
);

CREATE TABLE country
(
  id INTEGER,
  country VARCHAR(50)
);

create table bike_sales
(
  bike_id int,
  country_id int,
  quantity int,
  sales_date DATE
);

INSERT INTO bike VALUES(1,'XYZ',50000);
INSERT INTO bike VALUES(2,'ABC',70000);
INSERT INTO bike VALUES(3,'PQR',70000);

INSERT INTO country VALUES(1,'US');
INSERT INTO country VALUES(2,'Canada');
INSERT INTO country VALUES(3,'UK');

INSERT INTO bike_sales VALUES(1, 1, 5, '2018-01-01');
INSERT INTO bike_sales VALUES(1, 2, 10, '2018-02-01');
INSERT INTO bike_sales VALUES(1, 3, 7, '2018-03-01');
INSERT INTO bike_sales VALUES(2, 1, 9, '2018-04-01');
INSERT INTO bike_sales VALUES(2, 2, 8, '2018-05-01');
INSERT INTO bike_sales VALUES(2, 3, 4, '2018-06-01');
INSERT INTO bike_sales VALUES(3, 3, 4, '2019-06-01');

When I run this query:

SELECT c.country, b.name, (bs.quantity* b.price) as revenue 
FROM bike_sales bs 
  LEFT JOIN country c
    ON c.id = bs.country_id
  LEFT JOIN bike b
    ON b.id = bs.bike_id 
WHERE year(bs.sales_date) = '2018'
order by c.country, b.name

i get this output

+---------+------+---------+
| country | name | revenue |
+---------+------+---------+
| Canada  | ABC  |  560000 |
| Canada  | XYZ  |  500000 |
| UK      | ABC  |  280000 |
| UK      | PQR  |  280000 |
| UK      | XYZ  |  350000 |
| US      | ABC  |  630000 |
| US      | XYZ  |  250000 
+---------+------+---------+

I am calculating the country wide sales for 2018. I also want my result to display bikes that were sold in 2019 even though they were not sold in 2018.For example : There were no sales for 'PQR' in 'UK' in 2018 although it was sold in UK in 2019 hence I need that row as well in the output with 0 as revenue since there was no sales in 2018. How do I get this missing row ? 

| UK      | ABC  |  0 |

Upvotes: 3

Views: 67

Answers (1)

GMB
GMB

Reputation: 222442

Starting from your existing query, you can filter on 2018 and 2019 and use a case expression when computing the revenue:

select 
    c.country, 
    b.name, 
    case when year(bs.sales_date) = 2018 THEN bs.quantity * b.price else 0 end as revenue 
from 
    bike_sales bs
    left join country c on c.id = bs.country_id
    left join bike b on b.id = bs.bike_id 
where year(bs.sales_date) in (2018, 2019)
order by c.country, b.name

Demo on DB Fiddle:

| country | name | revenue |
| ------- | ---- | ------- |
| Canada  | ABC  | 560000  |
| Canada  | XYZ  | 500000  |
| UK      | ABC  | 280000  |
| UK      | PQR  | 0       |
| UK      | XYZ  | 350000  |
| US      | ABC  | 630000  |
| US      | XYZ  | 250000  |

Side note: mysql function year() returns a number (not a string).

Upvotes: 4

Related Questions