Posted in IT

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;

Advertisements

Author:

Siapa itu hilwa? Oh, itu pertanyaan yang sangat bagus! Hilwa itu adalah nickname dari seseorang yang bernama Wirda Hilwa, Tapi Wirda Hilwa itu masih punya nickname yang lain, yaitu wirda, dan iwa. Dari beberapa nickname itu Wirda Hilwa paling suka dipanggil Hilwa, karena arti dari hilwa itu adalah... hmm..., ga jadi deh, rahasia :P . [Sekarang serius mode:on ] Singkatnya aja, hilwa itu adalah seseorang yang hobi baca blog2 orang utk diambil manfaat & pelajaran dari blog tsb. Lalu terinspirasi pula utk membuat blog sendiri, utk berbagi yang bisa dibagi kpd orang lain. Semoga blog ini dapat bermanfaat buat yang baca. Amiin.

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