Convertion-query postgresql to mysql,date_part and sum case

Berikut contoh konversi query postgresql ke mysql, untuk date_part dan sum case atau count case.

Query Postgresql:

SELECT
date_part(‘month’, visit_date) AS schedule_month,
(CASE
WHEN(date_part(‘month’, visit_date) = 1) THEN ‘Jan’
WHEN(date_part(‘month’, visit_date) = 2) THEN ‘Feb’
WHEN(date_part(‘month’, visit_date) = 3) THEN ‘Mar’
WHEN(date_part(‘month’, visit_date) = 4) THEN ‘Apr’
WHEN(date_part(‘month’, visit_date) = 5) THEN ‘May’
WHEN(date_part(‘month’, visit_date) = 6) THEN ‘Jun’
WHEN(date_part(‘month’, visit_date) = 7) THEN ‘Jul’
WHEN(date_part(‘month’, visit_date) = 8) THEN ‘Aug’
WHEN(date_part(‘month’, visit_date) = 9) THEN ‘Sept’
WHEN(date_part(‘month’, visit_date) = 10) THEN ‘Okt’
WHEN(date_part(‘month’, visit_date) = 11) THEN ‘Nov’
WHEN(date_part(‘month’, visit_date) = 12) THEN ‘Dec’
END) as month_name,
COUNT (CASE WHEN(status = 0) THEN 1 END) AS scheduled,
COUNT (CASE WHEN(status = 0) THEN 1 END) AS scheduled,
COUNT (CASE WHEN(status = 4) THEN 1 END) AS unscheduled,
COUNT (CASE WHEN(status = 2) THEN 1 END) AS cancel
FROM
t_trx_pm_schedule
WHERE
(
date_part(‘year’, visit_date) = 2013
)
GROUP BY
date_part(‘month’, visit_date)
order by schedule_month asc;

———————————————————————————————————-

Query MySql:

SELECT
month(visit_date) AS schedule_month,
(CASE
WHEN(month(visit_date) = 1) THEN ‘Jan’
WHEN(month(visit_date) = 2) THEN ‘Feb’
WHEN(month(visit_date) = 3) THEN ‘Mar’
WHEN(month(visit_date) = 4) THEN ‘Apr’
WHEN(month(visit_date) = 5) THEN ‘May’
WHEN(month(visit_date) = 6) THEN ‘Jun’
WHEN(month(visit_date) = 7) THEN ‘Jul’
WHEN(month(visit_date) = 8) THEN ‘Aug’
WHEN(month(visit_date) = 9) THEN ‘Sept’
WHEN(month(visit_date) = 10) THEN ‘Okt’
WHEN(month(visit_date) = 11) THEN ‘Nov’
WHEN(month(visit_date) = 12) THEN ‘Dec’
END) as month_name,
SUM(IF (`status`,0,1)) AS scheduled,
SUM(IF (`status`,4,1)) AS unscheduled,
SUM(IF (`status`,2,1)) AS cancel
FROM
t_trx_pm_schedule
WHERE
(
year(visit_date) = 2013
)
GROUP BY
month(visit_date)
order by schedule_month asc;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s