Myujth

I have standard data that came from MySql statement

SELECT tipe.* 
 FROM 
 ( SELECT 'Menunggu Approve Atasan' AS tipe UNION
 SELECT 'Sudah Approved Atasan' UNION
 SELECT 'Dalam Proses Pengerjaan IT' UNION
 SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION
 SELECT 'Ditutup'
 ) AS tipe;
+---------------------------------------+
| tipe |
+---------------------------------------+
| Menunggu Approve Atasan |
| Sudah Approved Atasan |
| Dalam Proses Pengerjaan IT |
| Sudah Selesai Tapi Belum Ditutup User |
| Ditutup |
+---------------------------------------+
5 rows in set (0.02 sec)

Then I have data like this

SELECT requests.* 
 FROM 
 ( SELECT DATE(request.tanggal_permintaan) as tanggal_permintaan
 , request.`status`
 , COUNT(*) as total
 FROM request
 WHERE YEAR(request.tanggal_permintaan) = '2018'
 GROUP 
 BY DATE(request.tanggal_permintaan)
 , request.status
 ) AS requests;
 +--------------------+---------------------------------------+-------+
 | tanggal_permintaan | status | total |
 +--------------------+---------------------------------------+-------+
 | 2018-01-02 | Menunggu Approve Atasan | 1 |
 | 2018-01-02 | Ditutup | 4 |
 | 2018-01-03 | Ditutup | 1 |
 +--------------------+---------------------------------------+-------+
3 rows in set (0.02 sec)

As you can see form result of the second query, it gives me data based on date. But, I need to include all the standard data in those second query.
I need result like this:

+--------------------+---------------------------------------+-------+
| tanggal_permintaan | status | total |
+--------------------+---------------------------------------+-------+
| 2018-01-02 | Menunggu Approve Atasan | 1 |
| 2018-01-02 | Sudah Approved Atasan | 0 |
| 2018-01-02 | Dalam Proses Pengerjaan IT | 0 |
| 2018-01-02 | Sudah Selesai Tapi Belum Ditutup User | 0 |
| 2018-01-02 | Ditutup | 4 | // one day
| 2018-01-03 | Menunggu Approve Atasan | 0 |
| 2018-01-03 | Sudah Approved Atasan | 0 |
| 2018-01-03 | Dalam Proses Pengerjaan IT | 0 |
| 2018-01-03 | Sudah Selesai Tapi Belum Ditutup User | 0 |
| 2018-01-03 | Ditutup | 1 | // one day
+--------------------+---------------------------------------+-------+

What query that I use to get like this ?
So far, I just try the left outer join but no luck

SELECT requests.* FROM (
 SELECT
 DATE(request.tanggal_permintaan) as tanggal_permintaan,
 request.`status`,
 COUNT(*) as total
 FROM request
 WHERE YEAR(request.tanggal_permintaan) = '2018'
 GROUP BY DATE(request.tanggal_permintaan), request.status
) requests

LEFT OUTER JOIN (
 SELECT tipe.* FROM(
 SELECT 'Menunggu Approve Atasan' AS tipe UNION
 SELECT 'Sudah Approved Atasan' UNION 
 SELECT 'Dalam Proses Pengerjaan IT' UNION 
 SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
 SELECT 'Ditutup'
 ) tipe
)standard 
ON standard.tipe = requests.status

ORDER BY requests.tanggal_permintaan

To get that output, you need to create a CROSS JOIN of your tipe values with each of the distinct dates in your request table:

SELECT r.date, t.tipe
FROM (SELECT 'Menunggu Approve Atasan' AS tipe UNION
 SELECT 'Sudah Approved Atasan' UNION 
 SELECT 'Dalam Proses Pengerjaan IT' UNION 
 SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
 SELECT 'Ditutup'
 ) t
CROSS JOIN 
 (SELECT DISTINCT DATE(tanggal_permintaan) AS date 
 FROM request
 WHERE YEAR(tanggal_permintaan) = 2018) r

This will give you a table that looks like this:

| date | tipe |
+--------------------+---------------------------------------+
| 2018-01-02 | Menunggu Approve Atasan |
| 2018-01-02 | Sudah Approved Atasan |
| 2018-01-02 | Dalam Proses Pengerjaan IT |
| 2018-01-02 | Sudah Selesai Tapi Belum Ditutup User |
| 2018-01-02 | Ditutup |
| 2018-01-03 | Menunggu Approve Atasan |
| 2018-01-03 | Sudah Approved Atasan |
| 2018-01-03 | Dalam Proses Pengerjaan IT |
| 2018-01-03 | Sudah Selesai Tapi Belum Ditutup User |
| 2018-01-03 | Ditutup |

You can then LEFT JOIN that to your requests table to get the result you want:

SELECT s.date, s.tipe, COUNT(*) AS total
FROM (SELECT r.date, t.tipe
 FROM (SELECT 'Menunggu Approve Atasan' AS tipe UNION
 SELECT 'Sudah Approved Atasan' UNION 
 SELECT 'Dalam Proses Pengerjaan IT' UNION 
 SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
 SELECT 'Ditutup'
 ) t
 CROSS JOIN 
 (SELECT DISTINCT DATE(tanggal_permintaan) AS date 
 FROM request
 WHERE YEAR(tanggal_permintaan) = 2018) r
 ) s
LEFT JOIN request rq ON rq.status = s.tipe
GROUP BY s.date, s.tipe

• We can get all unique dates in the year 2018 in a Derived table. Year() function on the data does not allow it to use Indexes. I have changed it to use Between.. so that it can use the index.


• Similarly, get all unique statuses in a separate Derived Table.


• 
  Cross Join between them to get all the possible combinations.


• Now, use this all_combinations and left join to your main query, on status and date, to get the required result-set.

Try the following:

SELECT all_combinations.tanggal_permintaan, 
 all_combinations.tipe, 
 COALESCE(requests.total, 0) AS total 
FROM 
(
 SELECT all_tanggal_permintaan.tanggal_permintaan, 
 all_tipe.tipe 
 FROM 
 (SELECT DISTINCT DATE(tanggal_permintaan) as tanggal_permintaan
 FROM request 
 WHERE tanggal_permintaan BETWEEN '2018-01-01 00:00:00' AND 
 '2018-12-31 23:59:59'
 ) AS all_tanggal_permintaan 

 CROSS JOIN 

 (SELECT 'Menunggu Approve Atasan' AS tipe UNION ALL
 SELECT 'Sudah Approved Atasan' UNION ALL
 SELECT 'Dalam Proses Pengerjaan IT' UNION ALL
 SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION ALL
 SELECT 'Ditutup'
 ) AS all_tipe
) AS all_combinations 

LEFT OUTER JOIN

(
 SELECT
 DATE(tanggal_permintaan) as tanggal_permintaan,
 status,
 COUNT(*) AS total 
 FROM request
 WHERE tanggal_permintaan BETWEEN '2018-01-01 00:00:00' AND 
 '2018-12-31 23:59:59'
 GROUP BY DATE(tanggal_permintaan), status
) AS requests 
ON requests.tanggal_permintaan = all_combinations.tanggal_permintaan AND 
 requests.status = all_combinations.tipe

ORDER BY all_combinations.tanggal_permintaan

This is a guess, but it seems you need a list of status values, and it surprises me this isn't already in a table, but this might work:

SELECT
 g.tanggal_permintaan, g.total, s.status
FROM (
 SELECT DISTINCT
 request.status
 FROM request
) s
LEFT JOIN (
 SELECT
 DATE(request.tanggal_permintaan) AS tanggal_permintaan
 ,request.status
 ,COUNT(*) AS total
 FROM request
 WHERE request.tanggal_permintaan) >= '20180101'
 AND request.tanggal_permintaan) < '20190101'
 GROUP BY
 DATE(request.tanggal_permintaan)
 ,request.status
 ) g
 ON s.status = g.status

Thanks for brother @Nick and @Used_By_Already

Here it is my final query.
I create a unique id based concat to connecting them via left join.

SELECT standard.*, transactions.* FROM (
 SELECT CONCAT(r.date, '#', t.tipe) as 'id', r.date AS tanggal_permintaan, t.tipe
 FROM (
 SELECT DISTINCT
 request.status AS tipe
 FROM request
 ) t
 CROSS JOIN (
 SELECT DISTINCT DATE(tanggal_permintaan) AS date 
 FROM request
 WHERE YEAR(request.tanggal_permintaan) = '2018' 
 ) r

) AS standard

LEFT JOIN (
 SELECT CONCAT(requests.tanggal_permintaan, '#' , requests.status) AS 'id', requests.tanggal_permintaan, requests.status, requests.total 
 FROM (
 SELECT
 DATE(request.tanggal_permintaan) as tanggal_permintaan,
 request.`status`,
 COUNT(*) as total
 FROM request
 WHERE YEAR(request.tanggal_permintaan) = '2018'
 GROUP BY DATE(request.tanggal_permintaan), request.status
 ) requests
) AS transactions
ON transactions.id = standard.id

ORDER BY standard.id