Shrewd Shrewd - 10 days ago 6
MySQL Question

Multiple SUM in one Syntax

i have one small problem...

i have 2 separeted questions for SQL:

1:

SELECT SUM(iloscrec*ilosc/100) AS iloscsur, surowiec, Produkt
FROM receptura
JOIN produkcjadb ON(produkcjadb.Produkt = receptura.kolor)
WHERE data_zakonczenia >= CURRENT_DATE
GROUP BY surowiec


2:

SELECT SUM(ilosccal) AS ilosc, material, jednostka
FROM surowce
WHERE magazyn='G' || magazyn='K' || magazyn='S' || magazyn='P' || magazyn='PROUT' ||
magazyn='W' || magazyn='OR'
GROUP BY material


And now is my question, Can i connect this 2 question in one and SELECT rows from second question Where they Matched to 1 question WHERE

material (secondquestion) = surowiec (from1question)


im trying about 5hours but still i have sql syntax error

Thank you in advance !

Answer

Not sure if I understand the problem. It would help if you provide a script to create the structures and insert example data and if you show us the expected results, but anyway I guess you just want to use subqueries, e.g. something like this:

select ...
from (SELECT SUM(iloscrec*ilosc/100) AS iloscsur, surowiec, Produkt
        FROM receptura
        JOIN produkcjadb ON(produkcjadb.Produkt = receptura.kolor)
       WHERE data_zakonczenia >= CURRENT_DATE
       GROUP BY surowiec
      ) t1 join (
      SELECT SUM(ilosccal) AS ilosc, material, jednostka
        FROM surowce
       WHERE magazyn='G' || magazyn='K' || magazyn='S' || magazyn='P' || magazyn='PROUT' || magazyn='W'  || magazyn='OR'
       GROUP BY material
      ) t2
      on t2.material = t1.surowiec