hellope

Mysql

Jan 29th, 2017
141
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.41 KB | None | 0 0
  1. SELECT DISTINCT
  2.     m.ucode_outlet AS ucode_grup,
  3.     m.kode_outlet AS kode_grup,
  4.     ROUND(SUM(CASE
  5.                 WHEN tot.ucode_jns_brg = '40004' THEN tot.tot_qty
  6.                 ELSE 0
  7.             END),
  8.             2) AS 'qty_Jeruk',
  9.     ROUND(IFNULL(SUM(CASE
  10.                         WHEN tot.ucode_jns_brg = '40004' THEN tot.tot_qty
  11.                         ELSE 0
  12.                     END) / SUM(tot.tot_qty) * 100,
  13.                     0),
  14.             2) AS 'psn_Jeruk',
  15.     ROUND(SUM(CASE
  16.                 WHEN tot.ucode_jns_brg = '40005' THEN tot.tot_qty
  17.                 ELSE 0
  18.             END),
  19.             2) AS 'qty_Jeruk_Multi',
  20.     ROUND(IFNULL(SUM(CASE
  21.                         WHEN tot.ucode_jns_brg = '40005' THEN tot.tot_qty
  22.                         ELSE 0
  23.                     END) / SUM(tot.tot_qty) * 100,
  24.                     0),
  25.             2) AS 'psn_Jeruk_Multi',
  26.     ROUND(IFNULL(SUM(tot.tot_qty), 0), 2) AS total,
  27.     ROUND(IFNULL(SUM(tot.tot_qty) / SUM(tot.tot_qty) * 100,
  28.                     0),
  29.             2) AS total_psn
  30. FROM
  31.     tb_m_outlet m
  32.         LEFT OUTER JOIN
  33.     tp_m_tot_survey tot ON tot.kode_grup = m.kode_outlet
  34.         AND tot.id_proc = 'C9C48EF838B76B415266C553B7FC6ECC'
  35. WHERE
  36.     m.ucode_div LIKE '%%'
  37.         AND m.kode_outlet LIKE '%'
  38.         AND m.nama_outlet LIKE '%'
  39. GROUP BY m.ucode_outlet , m.kode_outlet , m.nama_outlet
  40. ORDER BY m.kode_outlet
  41. LIMIT 0 , 25
Advertisement
Add Comment
Please, Sign In to add comment