SQL One to Many Relationship - Query to retrieve data in JSON format

Question

I am a beginner in SQL and learning to design data base tables and querying the data for special cases.

I came up with below use case for learning one many to relationships for which I have designed three SQL tables shown below containing the information of the products manufactured by companies and it's sales info:

TABLE1: COMPANY TABLE - contains the name of the companies

ID  NAME
1   MICROSOFT 
2   APPLE
3   SAMSUNG

TABLE2: PRODUCTSERIES(Many to One relationship with COMPANY) - contains the series names of the products manufactured by companies

ID NAME         COMPANY_FK
1  WINDOWS        1
2  OFFICE         1
3  IPHONE         2
4  MACBOOK        2
5  GALAXY         3
6  GALAXYNOTE     3

TABLE3: PRODUCTS(Many to One relationship with PRODUCTSERIES) - contains actual products manufactured and their sales info

ID Name        total_manufactured    sold   PRODUCTSERIES_FK
1  GALAXY7         11                  1       5
2  GALAXY8         11                  1       5
3  GALAXYNOTE7     11                  1       6
4  GALAXYNOTE8     11                  1       6
5  OFFICE10        11                  1       2
6  OFFICE13        11                  1       2
7  IPHONE10        11                  1       3
8  IPHONE12        11                  1       3
9  MACBOOK MINI    11                  1       4
10 MACBOOK PRO     11                  1       4

Now I am trying to query these tables to obtain the sales percentage of the companies for each product series as shown in below example:

[{"company": MICROSOFT, "TOTAL_SALES%": 0.09, "SERIES": [{"WINDOWS_SALES": 0.09}, {"OFFICE_SALES": 0.09}]},
 {"company": APPLE, "TOTAL_SALES%": 0.09, "SERIES": [{"IPHONE_SALES": 0.09}, {"MACBOOK_SALES": 0.09}]},
 {"company": SAMSUNG, "TOTAL_SALES%": 0.09, "SERIES": [{"GALAXY_SALES": 0.09}, {"GALAXYNOTE_SALES": 0.09}]}]

NOTE: "TOTAL_SALES%" of a company is the average of the sales (sold/total_manufactured) of all the productseries owned by the company.

I am a beginner in SQL and have no clue how to get the output using SQL. Could you please share how can i frame my query or some relevant documentation to learn? Also can you please correct if my table design is not proper? I want to execute the query using PYTHON and I already know how to execute and retrieve the results using PYTHON if i know the query.

DB-FIDDLE URL:

https://www.db-fiddle.com/f/tJVQe3NqosqX2mkkUZrZMx/1

The first thing you should do is use a site such as https://www.db-fiddle.com/ and define your tables there with your data for people (including yourself) to try out their SQL. See: [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Booboo, Dec 21 '20 at 15:54
@Booboo added db-fiddle link: https://www.db-fiddle.com/f/tJVQe3NqosqX2mkkUZrZMx/1 — PJ47, Dec 21 '20 at 16:21

score 1 · Answer 1 · answered Dec 21 '20 at 17:41

I had to change the schema a bit to work with MySql (see below):

The SQL gets the percentages of each productseries. I will leave it as an exercise for you to get aggregate this at the company level:

SELECT c.company_name, ps.series_id, ps.series_name, SUM(p.sold) / sq.total_manufactured AS percentage
FROM products p
JOIN productseries ps ON p.product_series = ps.series_id
JOIN company c ON ps.series_company = c.company_id
JOIN (
  SELECT ps1.series_id, SUM(p1.manufactured) as total_manufactured
  FROM products p1 JOIN productseries ps1 ON p1.product_series = ps1.series_id
  GROUP BY ps1.series_id
) sq ON ps.series_id = sq.series_id
GROUP BY company_name, series_id, series_name
ORDER BY company_name, series_name

Schema (MySQL v5.7)

CREATE TABLE company (
    company_id INTEGER PRIMARY KEY,
    company_name VARCHAR(32) NOT NULL UNIQUE
);

company_name	series_id	series_name	percentage
APPLE	3	IPHONE	0.0909
APPLE	4	MACBOOK	0.0909
MICROSOFT	2	OFFICE	0.0909
MICROSOFT	1	WINDOWS	0.0909
SAMSUNG	5	GALAXY	0.0909
SAMSUNG	6	GALAXYNOTE	0.0909

View on DB Fiddle

Matthew Swallow · Answer 2 · 2020-12-21T20:44:44.953

SQL Select

In this case, you want to fetch information from a table. So you have to use the SELECT statement.

The format of the statement goes as follows: SELECT return_values FROM table_name WHERE conditions_statement

The return_values can be column names, or a value produced from SQL functions using the column names.

In your case, you will need to crunch together three different select statements:

Grab a list of every company_id (as this is foreign key in the productseries table) from every row that has a company_name equal to whichever company you want to work out the value for
Grab a list of the series_id (as this is foreign key in the products table) from the productseries table) from every row where the series_company for that row appears in the list of company_id's that we fetched previously
Fetch the sum of the sold columns, divided by the sum of the manufactured columns from every row in the products table where the product_series appears in the list of series_id's we fetched previously

In SQL you will need to use the IN keyword followed by a set of brackets including the next SQL statement. This keyword basically treats what is in the brackets as a list, and can be used as a condition (IE: to only select things from the table that are in the list). We can generate this list from the results of another SQL statement which is what you will need to do:

SELECT company_id FROM company WHERE company_name='APPLE'
SELECT series_id FROM productseries WHERE series_company IN (above_sql_here)
SELECT SUM(sold)/SUM(manufactured) FROM products WHERE product_series IN (above_sql_here)

The answer to your question

SELECT SUM(sold)/SUM(manufactured) FROM products WHERE product_series IN (SELECT series_id FROM productseries WHERE series_company IN (SELECT company_id FROM company WHERE company_name='APPLE'))

Another useful function in sql is CAST() which can effect the way the number returned is presented to us. If you require the number returned to be to a specific amount of decimal places, begin the above statement like this:

SELECT CAST(SUM(sold)/SUM(manufactured) as decimal(places_before_point, places_after_point)) FROM ....

Make sure to replace places_before_point and places_after_point with the desired amount of decimal places you want before and after the decimal point. (IE: decimal(1,2) would mean a float returned like 0.98). Creds to: juergen d's post.

One more thing to keep in mind is that you will have to write one of these statements for each company that you would like to find the value for.

An extra point that could be useful

If you are looking for just a value to be returned, use the above statement which returns just the percentage value. You could also use the JOIN keyword (which sticks the results of different SQL statements together) if presentation is an issue and you would like to, for example, display the name of the company followed by the number.

.... join_type JOIN ....

Replace join_type with the keyword that represents the type of join you want to do. More can be found on W3Schools, linked below.

The Most Useful Links

On the W3Schools SQL page, there is a list of every keyword with a page that explains to you everything you'll need to know about it when you click on it.

Two other useful links, if you're looking for somewhere to play around with SQL without doing anything live are:

DBFiddle (as seen above)
SQLFiddle (same sort of thing, different UI and a few different schemas)

SQL One to Many Relationship - Query to retrieve data in JSON format

2 Answers2