How to merge two queries into a subqueries

user_4978930 0
Viewed 107

I have database like this http://sqlfiddle.com/#!9/e52c43

First query is :

SELECT m.tanggal, sum(mi.qty) as totalMuatan, mi.idPlastik 
FROM tblMuatan m
LEFT JOIN tblMuatanIsi mi ON m.idMuatan = mi.idMuatan
WHERE m.tanggal='2020-03-15'
GROUP BY mi.idPlastik

The result is:

tanggal       totalMuatan    idPlastik
2020-03-15    85             1
2020-03-15    10             2

And second query is :

SELECT s.tanggal, sum(si.qty) as totalStok, si.idPlastik

FROM tblStok s

LEFT JOIN tblStokIsi si ON s.idStok = si.idStok
WHERE s.tanggal = '2020-03-15'
GROUP BY si.idPlastik

The result is :

tanggal       totalStok    idPlastik
2020-03-15    100          1
2020-03-15    200          2

I want to merge that 2 queries into single query with subquery

tanggal       totalStok     totalMuatan     netTotal    idPlastik
2020-03-15    100           85              15          1
2020-03-15    200           10              190         2

How to do the subquery in example above? Thank you for the support

subquery mysql sql
3 Answers
SELECT a.tanggal, 
       b.totalstok, 
       a.totalmuatan, 
       ( totalstok - totalmuatan ) netTotal, 
       a.idplastik 
FROM   (SELECT convert(varchar, m.tanggal, 23) tanggal, 
               Sum(mi.qty) AS totalMuatan, 
               mi.idplastik 
        FROM   #tblmuatan m 
               LEFT JOIN #tblmuatanisi mi 
                      ON m.idmuatan = mi.idmuatan 
        WHERE  m.tanggal = 1 
        GROUP  BY mi.idplastik, 
                  convert(varchar, m.tanggal, 23)) a 
       JOIN (SELECT convert(varchar, s.tanggal, 23) tanggal, 
                    Sum(si.qty) AS totalStok, 
                    si.idplastik 
             FROM   #tblstok s 
                    LEFT JOIN #tblstokisi si 
                           ON s.idstok = si.idstok 
             WHERE  s.tanggal = 1 
             GROUP  BY si.idplastik, 
                       convert(varchar, s.tanggal, 23)) b 
         ON a.idplastik = b.idplastik
user_39437300

Please try..

SELECT T1.tanggal, T2.totalStok, T1.totalMuatan, (T2.totalStok - T1.totalMuatan), T1.idPlastik
FROM
(SELECT m.tanggal, sum(mi.qty) as totalMuatan, mi.idPlastik 
FROM tblMuatan m
LEFT JOIN tblMuatanIsi mi ON m.idMuatan = mi.idMuatan
WHERE m.tanggal='2020-03-15'
GROUP BY mi.idPlastik) 
as T1,
(SELECT s.tanggal, sum(si.qty) as totalStok, si.idPlastik
FROM tblStok s
LEFT JOIN tblStokIsi si ON s.idStok = si.idStok
WHERE s.tanggal = '2020-03-15'
GROUP BY si.idPlastik)
as T2
WHERE
T1.tanggal = T2.tanggal;
user_122378370

Better to use COALESCE since you're using LEFT JOIN for the tables you are calculating SUM from to avoid no result for the records that have NULL values.

select m.tanggal, t.totalStok, sum(mi.qty) as totalMuatan, coalesce(t.totalStok,0)-coalesce(sum(mi.qty),0) as netTotal, mi.idPlastik 
from tblMuatan m
LEFT JOIN tblMuatanIsi mi ON m.idMuatan = mi.idMuatan
JOIN (SELECT s.tanggal, sum(si.qty) as totalStok, si.idPlastik 
FROM tblStok s 
LEFT JOIN tblStokIsi si ON s.idStok = si.idStok 
WHERE s.tanggal='2020-03-15'
GROUP BY s.tanggal,si.idPlastik) t on m.tanggal=t.tanggal and mi.idPlastik = t.idPlastik 
group by  m.tanggal, t.totalStok,mi.idPlastik
user_80835490
Related