I’ve created my table. I’m trying to craate a query that multiply and add sold_quantity from sold table and sale_price from on_sale table and called it R1 for now, and retail_price from product table and sold_quantity from sold table called it R2 for now.
In my query, I want to calculate my revenue. The catch is there’s two different date but one sale quantity. That means it’s kinda hard for me to distinguish twO types of sales( discounted sale, retail sale).
For example, on Feb.1st, I have a sale going on, I sold 10 quantity, and price sold is as sale_price and date is saved as sale_date, refer to On_sale table. On Feb.2, I sold 8 quantity , but price sold is saved as retail_price and saved as sold_date.
CREATE TABLE Sold ( store_number int(16) NOT NULL AUTO_INCREMENT, pid int(16) NOT NULL, sold_date date NOT NULL, sold_quantity int(16) NOT NULL, PRIMARY KEY (pid,store_number,sold_date) ); CREATE TABLE Store ( store_number int(16) NOT NULL AUTO_INCREMENT, phone_number varchar(16) NOT NULL DEFAULT '0', street_address varchar(250) NOT NULL, city_name varchar(250) NOT NULL, state varchar(250) NOT NULL, PRIMARY KEY (store_number) ); CREATE TABLE On_sale ( pid int(16) NOT NULL, sale_date date NOT NULL, sale_price float(16) NOT NULL, PRIMARY KEY (pid,sale_date) ); CREATE TABLE Product ( pid int(16) NOT NULL, product_name varchar(250) NOT NULL, retail_price float(16) NOT NULL, manufacture_name varchar(250) NOT NULL, PRIMARY KEY (pid) ); CREATE TABLE City ( city_name varchar(250) NOT NULL, state varchar(250) NOT NULL, population int(16) NOT NULL DEFAULT '0', PRIMARY KEY (city_name,state) );
This is what I want:
store_number phone_number street_address city_name state 1 # ### New York NY 2 # ### HOUSTON TX 3 # ### L.A CA
store_number PID SOLD DATE SOLD_QUANTITY 1 1 2/2/2017 3 2 2 2/3/2018 3 3 3 2/5/2019 4
PID SALE_DATE SALE PRICE 1 2/4/2018 2
PID PRODUCT NAME RETAIL_PRICE manufacture_name 1 XX 5 XXX 2 XX 4 XXX 3 XX 3 XXX
CITY_NAME STATE POPULATION New York NY 100 HOUSTON TX 200 L.A CA 201
YEAR REVENUE POPULATION 2017 15 (NEW YORK)SMALL 2018 14 (HOUSTON)MEDIUM 2019 12 (L.A) LARGE
Explanation of my data
This is very confusing. First I need to display year based on sold date and sale date, then calculate revenue. For example,in year 2018, the revneue is (2 from on_sale table’s sale_price) + (12 (3 * 4, 3 is the sold_quantity from sold_table, and 4 is retail_price) = 14.
The city size is separated by ranges, where 0>#<100 is small 100>=x<200 is medium and anything above 200 is large. the city name in the parenthesis is just to help track. The city is based on the city name and state in store table, and that is doen by comparing store_number on both sold table and store table
This requires me to join city table after querying to get R1(normal price) and R2(on sale price). Here’s what I got. I’m very lost:
SELECT year(s.sold_date) as yr, c.population, SUM(COALESCE(os.sale_price, p.retail_price) * s.sold_quantity) AS revenue, CASE WHEN population >= 0 AND population < 3700000 THEN 'small' WHEN population >= 3700000 AND population < 6700000 THEN 'medium' WHEN population >= 6700000 AND population < 9000000 THEN 'large' WHEN population >= 9000000 THEN 'extra_large' ELSE '-1' END AS cityCategory FROM Sold s JOIN Product p ON s.pid = p.pid JOIN Store st ON st.store_number = s.store_number LEFT JOIN On_sale os ON s.pid = os.pid JOIN city c ON c.city_name = st.city_name GROUP BY year(s.sold_date), c.population ORDER BY year(s.sold_date) ASC, c.population;
Here is an approach that might get the job done. The logic is to use aggregate subqueries to do the intermediate computations.
This query gets the revenue from the
On_sale table by year.
SELECT YEAR(sale_date) yr, SUM(sale_price) amt FROM On_sale GROUP BY YEAR(sale_date);
This other query gets the revenue per store and per year, using tables
SELECT s.store_number, YEAR(s.sold_date) yr, SUM(s.sold_quantity * p.retail_price) amt FROM Sold s INNER JOIN Product p ON p.pid = s.pid GROUP BY s.store_number, YEAR(sold_date);
Now we can
JOIN the results of these queries with the
Store tables. At the same time, we can split the cities into different size categories and use that to aggregate the results. I am using
LEFT JOIN in case one of the subqueries produces an empty resultset (else,
INNER JOIN is ok):
SELECT COALESCE(sa.yr, so.yr) sale_year, CASE WHEN c.population > 200 THEN 'large' WHEN c.population <= 200 AND c.population > 100 THEN 'medium' ELSE 'small' END as size_range, SUM(COALESCE(so.amt, 0) + COALESCE(sa.amt, 0)) revenue FROM City c INNER JOIN Store st ON st.state = c.state AND st.city_name = c.city_name LEFT JOIN ( SELECT s.store_number, YEAR(s.sold_date) yr, SUM(s.sold_quantity * p.retail_price) amt FROM Sold s INNER JOIN Product p ON p.pid = s.pid GROUP BY s.store_number, YEAR(sold_date) ) so ON so.store_number = st.store_number LEFT JOIN ( SELECT YEAR(sale_date) yr, SUM(sale_price) amt FROM On_sale GROUP BY YEAR(sale_date) ) sa ON sa.yr = so.yr GROUP BY sale_year, size_range ORDER BY sale_year, size_range
This demo on DB Fiddle with your sample data demonstrates the intermediate steps, and finally returns:
| sale_year | size_range | revenue | | --------- | ---------- | ------- | | 2017 | small | 15 | | 2018 | medium | 14 | | 2019 | large | 12 |