COUNT AND MULTI GROUP BY MONTH MYSQL-CodePudding

please help me, I want to display data with eloquent, before that I have to create a Raw Query to make it easier for me when I want to implement it to Eloquent. I want to get a result like this :

What I want

I've tried using this query, but the result is not what I want.

SELECT COUNT(t.pengaduan_id) jml, t.kanal_id, mpk.nama_kanal, DATE_FORMAT(t.created_at, '%M %Y') tgl
FROM tr_pengaduan AS t
LEFT JOIN ms_pengaduan_kanal mpk ON t.kanal_id = mpk.kanal_id
GROUP BY YEAR(t.created_at),MONTH(t.created_at) ORDER BY kanal_id

Result of the above query : Result

Here are the details from the table :

1. Table Tr_pengaduan : Tr_pengaduan

2. Table ms_pengaduan_kanal : ms_pengaduan_kanal

Is there a way to get the result I want with/without creating a procedure or function? I really appreciate it if you reply with Raw Query or Eloquent or both.

UPDATE : I tried @ProGu 's suggestion, but the result of the join is partially NULL.

SELECT COUNT(t.pengaduan_id) jml, t.kanal_id, mpk.nama_kanal, DATE_FORMAT(t.created_at, '%M %Y') tgl, mont.MONTH
FROM tr_pengaduan AS t
LEFT JOIN ms_pengaduan_kanal mpk ON t.kanal_id = mpk.kanal_id
LEFT JOIN (SELECT MONTH(CURRENT_DATE()) AS
MONTH
UNION SELECT MONTH(CURRENT_DATE())-1 AS
MONTH
UNION SELECT MONTH(CURRENT_DATE())-2 AS
MONTH
UNION SELECT MONTH(CURRENT_DATE())-3 AS
MONTH
UNION SELECT MONTH(CURRENT_DATE())-4 AS
MONTH) AS mont ON MONTH(t.created_at) = mont.MONTH
WHERE t.kanal_id = mpk.kanal_id
GROUP BY mpk.kanal_id ORDER BY kanal_id

Result : Update Result

CodePudding user response：

try something like this:

three parts in the query

month5 - represent current month and previous 4 months
t - id and names for result set
counts - counting results for last 5 months

SELECT COALESCE(counts.jml, 0) counts, t.kanal_id, t.nama_kanal, DATE_FORMAT(CURDATE() - INTERVAL month5.months MONTH, '%M %Y') tgl
FROM (
  SELECT 0 AS months
  UNION
  SELECT 1
  UNION
  SELECT 2
  UNION
  SELECT 3
  UNION
  SELECT 4
) month5
CROSS JOIN (
  SELECT t.kanal_id, MAX(mpk.nama_kanal) nama_kanal
  FROM tr_pengaduan t
  LEFT JOIN ms_pengaduan_kanal mpk ON t.kanal_id = mpk.kanal_id
  WHERE t.created_at BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY - INTERVAL 4 MONTH AND NOW()
  GROUP BY t.kanal_id
) t
LEFT JOIN (
  SELECT COUNT(t.pengaduan_id) jml, t.kanal_id, DATE(MAX(t.created_at)) tgl
  FROM tr_pengaduan AS t
  WHERE t.created_at BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY - INTERVAL 4 MONTH AND NOW()
  GROUP BY t.kanal_id, YEAR(t.created_at), MONTH(t.created_at) 
) counts ON LAST_DAY(counts.tgl) = LAST_DAY(CURDATE() - INTERVAL month5.months MONTH) AND t.kanal_id = counts.kanal_id
ORDER BY kanal_id, tgl