How can I find the restaurant name and the total number of orders for each in Jan 2021? The issue I'm facing is that the restaurant names and the orders are on separate tables as you can see from the code below.
create table orders (id integer, country text, customer_id integer,
restaurant_id INTEGER, date date, order_value integer);
create table customers (id integer, name text, country text);
create table restaurants (id integer, name text, country text);
INSERT INTO orders (
id,
country,
customer_id,
restaurant_id,
date,
order_value)
VALUES
(1, 'Pakistan', 1, 1, '2021-01-01', 400),
(2, 'Pakistan', 2, 1, '2021-01-01', 500),
(3, 'Pakistan', 4, 2, '2021-01-01', 300),
(4, 'Pakistan', 4, 3, '2021-01-05', 200),
(5, 'Pakistan', 5, 4, '2021-01-01', 250),
(6, 'Pakistan', 4, 1, '2021-01-09', 266),
(7, 'Pakistan', 3, 2, '2021-01-07', 322),
(1, 'Holland', 1, 1, '2021-01-01', 378),
(8, 'Pakistan', 1, 3, '2021-06-01', 289),
(2, 'Holland', 1, 1, '2021-08-01', 480),
(9, 'Pakistan', 1, 1, '2021-03-01', 580),
(10, 'Pakistan', 3, 2, '2021-07-01', 360),
(3, 'Holland', 1, 1, '2021-09-01', 550),
(11, 'Pakistan', 4, 3, '2021-04-01', 991),
(12, 'Pakistan', 5, 1, '2021-04-01', 875),
(4, 'Holland', 1, 1, '2021-03-02', 250),
(13, 'Pakistan', 1, 1, '2021-08-01', 150),
(14, 'Pakistan', 1, 2, '2021-09-01', 290),
(5, 'Holland', 1, 1, '2021-07-01', 240),
(15, 'Pakistan', 1, 3, '2021-03-01', 780),
(16, 'Pakistan', 1, 4, '2021-06-01', 987),
(6, 'Holland', 1, 1, '2021-05-03', 457),
(17, 'Pakistan', 1, 4, '2021-05-04', 258);
INSERT INTO customers (
id,
name,
country)
VALUES
(1, 'Steven Smith', 'Pakistan'),
(2, 'Arthur Chen', 'Holland'),
(3, 'Michael Wren', 'Pakistan'),
(4, 'John Almagro', 'Pakistan'),
(5, 'Luke Pablo', 'Pakistan'),
(6, 'Monty Tron', 'Pakistan');
INSERT INTO restaurants (
id,
name,
country)
VALUES
(1, 'KFC', 'Pakistan'),
(2, "McDonald's", 'Holland'),
(3, 'Howdy', 'Pakistan'),
(4, 'Kitchen Cuisine', 'Pakistan'),
(5, 'JFC', 'Pakistan'),
(6,'Hardees','Pakistan');
I learned about JOIN functions but I'm not able to join the dots.
CodePudding user response：
Joining two table, is made by telling the database, which rows belong together. this is defined in the ON clause, where the joning columns are mentioned.
the WHERE clause is the same as in the last query it removes all rows that have not the right year and month.
The Group By has here three columns, because the restaurant_id has always the same value. We could also had added a aggregation function to the columns, which would have the same effect
SELECT r.name, r.country, COUNT(*) Total_orders FROM orders o JOIN restaurants r ON o.restaurant_id = r.id WHERE YEAR(`date`)= 2021 AND MONTH(`date`)= 1 GROUP BY restaurant_id,r.name,r.country
name | country | Total_orders :-------------- | :------- | -----------: KFC | Pakistan | 4 McDonald's | Holland | 2 Howdy | Pakistan | 1 Kitchen Cuisine | Pakistan | 1
SELECT MAX(r.name) name, MAX(r.country) country, COUNT(*) Total_orders FROM orders o JOIN restaurants r ON o.restaurant_id = r.id WHERE YEAR(`date`)= 2021 AND MONTH(`date`)= 1 GROUP BY restaurant_idname | country | Total_orders :-------------- | :------- | -----------: KFC | Pakistan | 4 McDonald's | Holland | 2 Howdy | Pakistan | 1 Kitchen Cuisine | Pakistan | 1
db<>fiddle here
CodePudding user response：
SELECT -- MySQL SELECT statement
MAX(r.name) `Hotel Name`, -- column name as Hotel Name from table restaurants, alias as r
COUNT(*) `Number Of Orders`, -- count all the records for table orders, alias o
SUM(o.order_value) `Total Order Value`, -- SUM all the order_values for match records
MAX(r.country) `Country` -- column country
FROM
orders o -- Running operations on table orders and set alias o
INNER JOIN restaurants r ON o.restaurant_id = r.id -- INNER JOIN second table restaurants as r and joining
-- two tables using field r.id (restaurants primary key id)
-- and o.restaurant_id (foreign key of restaurant's primary key id)
WHERE -- setting condition
YEAR(o. `date`) = 2021 -- Year must be 2021
AND MONTH(o. `date`) = 1 -- and month must be JAN or 1
GROUP BY -- group all same hotels id
o.restaurant_id;
| Hotel Name | Number Of Orders | Total Order Value | Country |
|-----------------|------------------|-------------------|----------|
| KFC | 4 | 1544 | Pakistan |
| McDonald's | 2 | 622 | Holland |
| Howdy | 1 | 200 | Pakistan |
| Kitchen Cuisine | 1 | 250 | Pakistan |
HINT -
To join the two different tables we'd need two columns which has same values, common or has some linking.
Here table orders has restaurant_id which is a foreign key of table restaurants (id). In other word, we can use those id to identify the restaurant details by querying table restaurants.
hence to join table orders and restaurants, we should use column id from table restaurants and column restaurant_id from table orders.
Now since orders table has multiple rows with the same restaurant_id; it's better group them together to make as buckets.
Once we use GROUP BY a column; MySQL group them in a bucket which has same values or given conditions.
Any aggregated statement like SUM, AVG, COUNT, MAX, MIN, etc. would take those individual buckets as logical table and perform the operations.