MYSQL + PHP, How to add items from different SUM () tables?

Question

MYSQL + PHP, How to add items from different SUM () tables?

Navigation

#1 by (1 votes)

0

I have two tables:

table_enights

| id | descricao                 | quantidade |
| 1  | Maquina Prensa            | 1          |
| 2  | Maquina Secadora          | 2          |

table_bom_itens

| id | id_itens | codigo | quantidade |
| 1  | 1        | mp001  | 1          |
| 2  | 1        | mp002  | 2          |
| 3  | 1        | fx001  | 1          |
| 4  | 1        | fx002  | 5          |
| 5  | 2        | ms001  | 1          |
| 6  | 2        | ms002  | 2          |
| 7  | 2        | ms003  | 1          |
| 8  | 2        | ms004  | 1          |
| 9  | 2        | fx001  | 4          |
| 10 | 2        | fx002  | 1          |

I'm running the following query:

SELECT 
    SUM(tb.quantidade*quantidade)
FROM
    tabela_bom_itens
GROUP BY
    codigo;

What is to happen to tabela_itens is the parent product, and in tabela_bom_itens would be the children. In tabela_bom_itens would be the amount of parts that goes to each parent item. So to know the amount of pieces that goes into the parent product was to multiply the amount of parent product for each child product, and if the same items appear in both, group and report the sum value of the items.

The expected result is:

| codigo | quantidade |
| mp001  | 1          |
| mp002  | 2          |
| ms001  | 2          |
| ms002  | 4          |
| ms003  | 2          |
| ms004  | 2          |
| fx001  | 9          |
| fx002  | 10         |

But what's coming back has nothing to do with what's wrong with my query?

php mysql

asked by anonymous 25.05.2017 / 18:44

1 answer

Error "Trying to get property of non-object" when using mysqli_stmt_fetch Missing connection to server via HttpURLConnection

score 1 · Accepted Answer

Note: I have a limitation of not having a MySQL available for testing, so I'm doing the tests in SQLite; so if any syntax is incorrect, please correct / notify me

To know the relationship between parent products and good children, we need to join together. So, for me to associate all the lines of tabela_bom_itens with the lines of tabela_itens , I make an inner join:

SELECT
    *
FROM
    tabela_itens pai
    INNER JOIN tabela_bom_itens filho
        ON (pai.id = filho.id_itens)

The inner join is indicated by the INNER JOIN syntax. When a joint is made, it is always good to say what the join condition is. In this case, the join condition is when id of parent is equal to id_itens of child. This join condition is indicated in the ON (pai.id = filho.id_itens) part.

Do the multiplication to know how much of each child part goes to the parent product (not yet grouped), is to do the following multiplication:

SELECT
    pai.quantidade * filho.quantidade as quantidade_multiplicada,
    *
FROM
    tabela_itens pai
    INNER JOIN tabela_bom_itens filho
        ON (pai.id = filho.id_itens)

Note that the first column (identified by quantidade_multiplicada ) is the desired value to add. Now we need to add this multiplication by grouping it with codigo :

SELECT
    filho.codigo,
    SUM(pai.quantidade * filho.quantidade) as quantidade_venda
FROM
    tabela_itens pai
    INNER JOIN tabela_bom_itens filho
        ON (pai.id = filho.id_itens)
GROUP BY filho.codigo

In column quantidade_venda , we have how much of each item was sold.

In this case, I believe that the expected result has a misunderstanding for the product fx002 , since we have sold it 5 times for the item Maquina Prensa (quantity multiplier 1) and 1 time for the item Maquina Secadora (quantity multiplier 2), which would result in 7.

Here is the result of my query:

| codigo | quantidade_venda |
| fx001  | 9                |
| fx002  | 7                |
| mp001  | 1                |
| mp002  | 2                |
| ms001  | 2                |
| ms002  | 4                |
| ms003  | 2                |
| ms004  | 2                |