Eliminate redundancy when SQL has similar queries (without WITH)

I have a question about SQL statements.
Suppose you have the following table:

In response, I would like to submit the stock data of paint only and add the total weight of each color code.
The image has the following results:

For that reason, I wrote the following query.

SELECT
    X.*,
    Y. Total Weight
from
    (
        SELECT
            Color code,
            Weight
        from
            stock
            INNER JOIN
                item master
            ON Inventory. Item Code = Item Master. Item Code
        WHERE
            Item Master.Classification = 'Paint'
    ) X
    LEFT JOIN
        (
            SELECT
                Color code,
                SUM (Weight) as Gross Weight
            from
                stock
                INNER JOIN
                    item master
                ON Inventory. Item Code = Item Master. Item Code
            WHERE
                Item Master.Classification = 'Paint'
            GROUP BY
                inventory.Color code
        )Y
    ON X. Color Code = Y. Color Code

In order to find the total weight of each color code, similar subqueries are LEFT JOINed to each other.
Is there a way to clear up these similar queries and get the results you want?

SELECT
    X.*,
    SELECT SUM (weight)~~

I tried various things like this, thinking that I could do a similar subquery without JOIN, but it didn't work.
Is this the only way I can get it?
Also, please do not use the WITH section.
Please be aware that this SQL is not actually running and may result in errors.

sql

2022-09-30 16:35

3 Answers

For MySQL 8.0.20, group by color code and weight, and use the Window function to determine the total weight.

SELECT
  `Color code`,
  `Weight',
  Round(
    SUM(SUM(`Weight`))
    OVER(PARTITION BY`COLOR CODE`), 1) AS`Total weight per color code`
from 
  `Inventory`
WHERE
  `item code `IN
    (SELECT `item code `FROM`item master `WHERE` classification `='paint')
GROUP BY `color code`, `weight`;


+--------------------+--------+-----------------------------------+
| Color Cord | Weight | Total Weight per Color Cord |
+--------------------+--------+-----------------------------------+
|                100 |    1.2 |                               4.7 |
|                100 |    3.5 |                               4.7 |
|                300 |    1.8 |                               1.8 |
+--------------------+--------+-----------------------------------+

2022-09-30 16:35

You can do it with a correlation subquery

SELECT
    X. Color Code,
    X. Weight,
    (SELECT SUM FROM Inventory Y WHERE Y. Color Code = X. Color Code AND Y. Item Code = X. Item Code) Total AS Weight
from
    Inventory X
WHERE 
    X. Item Code IN (SELECT Item Code FROM Item Master WHERE Classification = 'Paint')

2022-09-30 16:35

Creating a view of paint inventory eliminates a bit of redundancy.

[SQL for creating views]

create view paint inventory
 as select A. color code, A. weight
    from Inventory A, Item Master B
    where A. item code = B. item code AND B. classification = 'paint';

[Search SQL]

 select A. color code, A. weight,
                               from Paint Inventory X
                               where A. color code = X. color code
                               group by X. color code) Total weight per AS color code
from paint inventory A;

Creating a view of the total weight per color code makes SQL search easier.

[SQL for creating views]

Total weight per create view color code
 as select color code, sum (weight) Total weight per color code
    from inventory
    group by color code;

[Search SQL]

 select A. color code, A. weight, X. total weight per color code
from paint inventory A, total weight X for each color cord
where A. color code = X. color code;

2022-09-30 16:35

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656