MYSQL Concat,Left Join,Group Solution

Question

I have two tables

1) cawangan_tbl

 mysql> select * from cawangan_tbl
+------------+---------------------------+------------+-------+
| IdCawangan | NamaCawangan              | IdParlimen | IdDun |
+------------+---------------------------+------------+-------+
|          3 | Kampung Titi Tinggi Hilir |          1 |     6 |
|          6 | Kampung Titi Rendah Hilir |          3 |     6 |
|          7 | Kampung Titi Kiri Hilir   |          3 |     6 |
|          8 | Kampung Titi Kanan Hilir  |          3 |     6 |
+------------+---------------------------+------------+-------+

2)minit_tbl

select IdMinit,IdDun,IdCawangan,IdParlimen,NoMinit,Status from minit_tbl
+---------+-------+------------+------------+---------+--------+
| IdMinit | IdDun | IdCawangan | IdParlimen | NoMinit | Status |
+---------+-------+------------+------------+---------+--------+
|      14 |     6 |          3 |          3 | 1       | OnTime |
|      15 |     6 |          3 |          3 | 2       | OnTime |
|      16 |     6 |          3 |          3 | 4       | Late   |
|      17 |     6 |          7 |          3 | 1       | Late   |
+---------+-------+------------+------------+---------+--------+

Right now, my output is like this :

select C.IdCawangan,
CONCAT(case when M.NoMinit = 1 then (CONCAT(M.NoMinit,'--> ',M.status)) else 0 end) MonthJan,
CONCAT(case when M.NoMinit = 2 then (CONCAT(M.NoMinit,'--> ',M.status)) else 0 end) MonthFeb,
sum(case when M.NoMinit = 3 then M.NoMinit else 0 end) MonthMac,
sum(case when M.NoMinit = 4 then M.NoMinit else 0 end) MonthApril,
sum(case when M.NoMinit = 5 then M.NoMinit else 0 end) MonthMei,
sum(case when M.NoMinit = 6 then M.NoMinit else 0 end) MonthJun
from cawangan_tbl C left join minit_tbl M 
on M.IdCawangan = C.IdCawangan
group by C.IdCawangan


+------------+-------------+----------+----------+------------+----------+----------+
| IdCawangan | MonthJan    | MonthFeb | MonthMac | MonthApril | MonthMei | MonthJun |
+------------+-------------+----------+----------+------------+----------+----------+
|          3 | 1--> OnTime | 0        |        0 |          4 |        0 |        0 |
|          6 | 0           | 0        |        0 |          0 |        0 |        0 |
|          7 | 1--> Late   | 0        |        0 |          0 |        0 |        0 |
|          8 | 0           | 0        |        0 |          0 |        0 |        0 |
+------------+-------------+----------+----------+------------+----------+----------+

Supposedly, my output will be like this for MonthFeb :

+------------+-------------+-------------+----------+------------+----------+----------+
| IdCawangan | MonthJan    | MonthFeb    | MonthMac | MonthApril | MonthMei | MonthJun |
+------------+-------------+-------------+----------+------------+----------+----------+
|          3 | 1--> OnTime | 2--> OnTime |        0 |          4 |        0 |        0 |
|          6 | 0           | 0           |        0 |          0 |        0 |        0 |
|          7 | 1--> Late   | 0           |        0 |          0 |        0 |        0 |
|          8 | 0           | 0           |        0 |          0 |        0 |        0 |
+------------+-------------+-------------+----------+------------+----------+----------+

Can someone point to me the right code how to show like MonthJan for each next month?

score 0 · Answer 1 · answered Nov 14 '15 at 17:55

Added some cases and aggregate function in your original query.

Check this:

select C.IdCawangan,
coalesce(case when max(case when M.NoMinit = 1 then M.NoMinit else 0 end)>0 then 
    concat(max(case when M.NoMinit = 1 then M.NoMinit else 0 end),'-->',M.status) end,0)  MonthJan,
coalesce(case when max(case when M.NoMinit = 2 then M.NoMinit else 0 end) >0 then
    concat(max(case when M.NoMinit = 2 then M.NoMinit else 0 end),'-->',M.status) end,0) MonthFeb,
sum(case when M.NoMinit = 3 then M.NoMinit else 0 end) MonthMac,
sum(case when M.NoMinit = 4 then M.NoMinit else 0 end) MonthApril,
sum(case when M.NoMinit = 5 then M.NoMinit else 0 end) MonthMei,
sum(case when M.NoMinit = 6 then M.NoMinit else 0 end) MonthJun
from cawangan_tbl C left join minit_tbl M 
on M.IdCawangan = C.IdCawangan
group by C.IdCawangan;

hope this help :)

MYSQL Concat,Left Join,Group Solution

1 Answers1