Generate query using 5 tables

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:

SAMPLE DATA:

Store table:

store_number  phone_number  street_address city_name state 
     1           #             ###          New York    NY
     2           #             ###          HOUSTON     TX
     3           #             ###          L.A         CA

Sold Table:

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

On_sale Table:

PID  SALE_DATE    SALE PRICE  
1      2/4/2018    2

Product Table:

PID  PRODUCT NAME  RETAIL_PRICE manufacture_name
1       XX           5              XXX          
2      XX          4             XXX       
3       XX           3              XXX       

City table:

CITY_NAME  STATE    POPULATION  
New York   NY    100
HOUSTON    TX    200
L.A        CA    201

EXPEXTED RESULT:

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;

Answer

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 Sold and Product:

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 City and 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      |