Sample Header Ad - 728x90

Generate report by cross join between two tables

0 votes
0 answers
153 views
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.
Asked by NAS BS (1 rep)
May 19, 2021, 03:10 AM
Last activity: May 19, 2021, 07:42 PM