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
Last activity: May 19, 2021, 07:42 PM