SQL Query Sum, Multiply and combine 5 tables then group

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 TABLES
store_number  phone_number  street_address city_name state 
     1           #             ###          New York    NY
     2           #             ###          HOUSTON     TX
     3           #             ###          L.A         CA


Sold Tables
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 Tables
PID  SALE_DATE    SALE PRICE  
1      2/4/2018    2

Product Tables
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

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 Sold.sold_quantity,
       Product.retail_price AS S1
    SUM(Product.retail_price*Sold.sold_quantity) AS R1

FROM  Sold LEFT JOIN Product
ON     Sold.pid=Product.pid

SELECT Sold.sold_quantity,
       On_sale.sale_price AS S2
    SUM(On_sale.sale_price) AS R2

FROM  Sold LEFT JOIN On_sale
ON     Sold.pid=On_sale.pid

Answer

I don’t understand the use of LEFT JOIN in your queries. You seem to have a well-defined data model and the ids should line up.

You specifically say that you want to add R1 and R2, so the query you describe looks like this:

SELECT year(s.sold_date) as yr, c.population,
       (SUM(p.retail_price * s.sold_quantity) +
        SUM(os.sale_price * s.sold_quantity)
       ) AS revenue
FROM  Sold s JOIN
      Product p
      ON s.pid = p.pid JOIN
      city c
      ON c.city_name = p.manufacture_name LEFT JOIN
      On_sale os
      ON s.pid = os.pid AND
         os.sale_date = s.sold_date
GROUP BY year(s.sold_date), c.population
ORDER BY year(s.sold_date), c.population;

Note that this adds an extra condition saying that the dates match for the on_sale table. That makes sense to me.

Adding these two revenue numbers seems very suspicious to me. I would expect that you would want the sales price, if it exists, and otherwise the retail price. If so then the calculation would be:

       SUM(COALESCE(os.sale_price, p.retail_price) * s.sold_quantity) AS revenue

If you want the sum of the discount amounts, then the difference makes sense.

Advertisement

Answer