I wanted to generate report between two tables by using CROSS JOIN. Below shows the details of table.
Table 1: kod_jawatan
| jawatan_id | jawatan_kod | jawatan_nama |
|---|
Table 2: tpermohonan
| mohon_id | pemohon_id | mohon_tarikh | mohon_tarikhluput |
|---|
Here's the code that I've tried but it didn't work.
SELECT jawatan_nama, MONTHNAME(mohon_tarikh) AS mname,
count(jawatan_id) AS total
FROM kod_jawatan
CROSS JOIN tpermohonan
WHERE year(mohon_tarikh) = '2019'
GROUP BY MONTH(mohon_tarikh)
UNION ALL
SELECT 'Jumlah',
count(jawatan_id) AS total
FROM tpermohonan
CROSS JOIN kod_jawatan
WHERE year(mohon_tarikh) = '2019'
This is the output that I want:
| Bil | Jawatan | Jan | Feb | March | April | May | June | July | August | September | Oct | Nov | Dec | Total |
|---|
How do i get the output I want? Much appreciated for your helps.
