CODEWITHSUNDEEP
mysql
Gagantous
Gagantous

Reputation: 518

Combine two result query into one

I have query like this ( i have complex queries with join and union in both queries )

SELECT 
            id, 
            COUNT(id) ct_id, 
            SUM(kl) sum_kl, 
            SUM(CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
            SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
        FROM (
            SELECT 
                id_kondisi as id, 
                tgl_kondisi,
                nilai_potensi_kerugian AS kl 
            FROM laporan_kondisi 
            UNION  
            SELECT 
                id_sub_kondisi, 
                tgl_kondisi, 
                nilai_potensi_kerugian 
            FROM laporan_kondisi 
        ) merged_table  GROUP BY id ORDER BY id;

Result

+------+-------+----------+------+----------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  |
+------+-------+----------+------+----------+
| 01   |     3 | 20000000 |    3 | 20000000 |
| 0101 |     2 |  9000000 |    2 |  9000000 |
| 0102 |     2 | 11000000 |    2 | 11000000 |
| 02   |     1 |        0 |    0 |        0 |
| 0201 |     1 |        0 |    0 |        0 |
| 0202 |     1 |        0 |    0 |        0 |
| 0203 |     1 |        0 |    0 |        0 |
| 03   |     2 |  4000000 |    2 |  4000000 |
| 0301 |     1 |        0 |    1 |        0 |
| 0302 |     2 |  4000000 |    2 |  4000000 |
| 0303 |     1 |        0 |    1 |        0 |
+------+-------+----------+------+----------+

And another query 

SELECT  
        id,
        SUM(CASE WHEN tgl_tindak_lanjut="2017-09-14" THEN 1 ELSE 0 end ) as count_all_09,
        SUM( CASE WHEN tgl_tindak_lanjut="2017-09-13" THEN 1 ELSE 0 END) as count_09_13 
    FROM ( 
        SELECT 
            a.id_kondisi as id, 
            d.tgl_tindak_lanjut as tgl_tindak_lanjut 
        FROM
            laporan_kondisi a 
        LEFT OUTER JOIN  
            laporan_sebab b 
        ON 
            a.id = b.id_laporan_kondisi 
        LEFT OUTER JOIN  
            laporan_rekomendasi c 
        ON 
            b.id = c.id_laporan_sebab 
        LEFT OUTER JOIN 
            laporan_tindak_lanjut d 
        ON 
            c.id = d.id_laporan_rekomendasi  
        UNION  
        SELECT 
            a2.id_sub_kondisi,
            d2.tgl_tindak_lanjut as tgl_tindak_lanjut 
        FROM
            laporan_kondisi a2 
        LEFT OUTER JOIN 
            laporan_sebab b2 
        ON  
            a2.id = b2.id_laporan_kondisi 
        LEFT OUTER JOIN 
            laporan_rekomendasi c2 
        ON 
            b2.id = c2.id_laporan_sebab 
        LEFT OUTER join  
            laporan_tindak_lanjut d2 
        ON 
            c2.id = d2.id_laporan_rekomendasi  
    ) merged_table GROUP BY id

Result 

+------+--------------+-------------+
| id   | count_all_09 | count_09_13 |
+------+--------------+-------------+
| 01   |            0 |           1 |
| 0101 |            0 |           1 |
| 0102 |            0 |           0 |
| 02   |            0 |           0 |
| 0201 |            0 |           0 |
| 0202 |            0 |           0 |
| 0203 |            0 |           0 |
| 03   |            0 |           0 |
| 0301 |            0 |           0 |
| 0302 |            0 |           0 |
| 0303 |            0 |           0 |
+------+--------------+-------------+

Is this posible to combine the table just like this ?

+------+-------+----------+------+----------+--------------+-------------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  | count_all_09 | count_09_13 |
+------+-------+----------+------+----------+--------------+-------------+
| 01   |     3 | 20000000 |    3 | 20000000 |     0        |       1     |
| 0101 |     2 |  9000000 |    2 |  9000000 |     0        |       1     |
| 0102 |     2 | 11000000 |    2 | 11000000 |     0        |       0     |
| 02   |     1 |        0 |    0 |        0 |     0        |       0     |
| 0201 |     1 |        0 |    0 |        0 |     0        |       0     |
| 0202 |     1 |        0 |    0 |        0 |     0        |       0     |
| 0203 |     1 |        0 |    0 |        0 |     0        |       0     |
| 03   |     2 |  4000000 |    2 |  4000000 |     0        |       0     |
| 0301 |     1 |        0 |    1 |        0 |     0        |       0     |
| 0302 |     2 |  4000000 |    2 |  4000000 |     0        |       0     |
| 0303 |     1 |        0 |    1 |        0 |     0        |       0     |
+------+-------+----------+------+----------+--------------+-------------+

I have tried JOIN and Many other but it only give one single result. Is this possible to do the result just like this ? Or this query is just give us none result ?

Upvotes: 0

Views: 26

Answers (1)

sagi
sagi

Reputation: 40471

It's hard to say if by adding all the second query logic the result will be impacted, because you didn't provide any info about what this query does or about the actual data. What you can do, which should always work (not recommended if you're planning on reusing this query, and if performance is an issue, that is not recommended either) , is just join the entire queries .

It will look like this:

SELECT t.*,s.count_all_09,s.count_09_13
FROM (FIRST QUERY HERE) t
JOIN (SECOND QUERY HERE) s
 ON(t.id = s.id)

Upvotes: 1

Related Questions