cahyadsn

Total GrandTotal Grouping

Sep 15th, 2017
206
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 4.13 KB | None | 0 0
  1. DROP TABLE IF EXISTS data;
  2. CREATE TABLE IF NOT EXISTS data(
  3.     id INT AUTO_INCREMENT PRIMARY KEY,
  4.     minggu_ke INT,
  5.     output_press FLOAT,
  6.     tanggal DATE
  7. );
  8.  
  9. INSERT INTO data(minggu_ke,output_press,tanggal)
  10. VALUES
  11. (31,2631.0,'2017-08-01'),
  12. (31,3043.0,'2017-08-02'),
  13. (31,4724.0,'2017-08-03'),
  14. (31,1931.0,'2017-08-04'),
  15. (31,4931.0,'2017-08-05'),
  16. (32,5233.0,'2017-08-06'),
  17. (32,2935.0,'2017-08-07'),
  18. (32,1941.0,'2017-08-08'),
  19. (32,3531.0,'2017-08-09'),
  20. (32,3982.0,'2017-08-10'),
  21. (32,2978.0,'2017-08-11'),
  22. (32,1439.0,'2017-08-12'),
  23. (33,5530.0,'2017-08-13'),
  24. (33,4236.0,'2017-08-14');
  25.  
  26. SELECT * FROM data;
  27. +----+-----------+--------------+------------+
  28. | id | minggu_ke | output_press | tanggal    |
  29. +----+-----------+--------------+------------+
  30. |  1 |        31 |         2631 | 2017-08-01 |
  31. |  2 |        31 |         3043 | 2017-08-02 |
  32. |  3 |        31 |         4724 | 2017-08-03 |
  33. |  4 |        31 |         1931 | 2017-08-04 |
  34. |  5 |        31 |         4931 | 2017-08-05 |
  35. |  6 |        32 |         5233 | 2017-08-06 |
  36. |  7 |        32 |         2935 | 2017-08-07 |
  37. |  8 |        32 |         1941 | 2017-08-08 |
  38. |  9 |        32 |         3531 | 2017-08-09 |
  39. | 10 |        32 |         3982 | 2017-08-10 |
  40. | 11 |        32 |         2978 | 2017-08-11 |
  41. | 12 |        32 |         1439 | 2017-08-12 |
  42. | 13 |        33 |         5530 | 2017-08-13 |
  43. | 14 |        33 |         4236 | 2017-08-14 |
  44. +----+-----------+--------------+------------+
  45.  
  46. SELECT
  47.   IF(COALESCE(tanggal,'Total')='Total','',COALESCE(minggu_ke,'GrandTotal')) AS minggu_ke,
  48.   IF(COALESCE(minggu_ke,'GrandTotal')='GrandTotal','GrandTotal:',COALESCE(tanggal,'Total:')) AS tanggal,
  49.   SUM(output_press) AS output_press
  50. FROM data
  51. GROUP BY minggu_ke,tanggal WITH ROLLUP;
  52.  
  53. +-----------+-------------+--------------+
  54. | minggu_ke | tanggal     | output_press |
  55. +-----------+-------------+--------------+
  56. | 31        | 2017-08-01  |         2631 |
  57. | 31        | 2017-08-02  |         3043 |
  58. | 31        | 2017-08-03  |         4724 |
  59. | 31        | 2017-08-04  |         1931 |
  60. | 31        | 2017-08-05  |         4931 |
  61. |           | Total:      |        17260 |
  62. | 32        | 2017-08-06  |         5233 |
  63. | 32        | 2017-08-07  |         2935 |
  64. | 32        | 2017-08-08  |         1941 |
  65. | 32        | 2017-08-09  |         3531 |
  66. | 32        | 2017-08-10  |         3982 |
  67. | 32        | 2017-08-11  |         2978 |
  68. | 32        | 2017-08-12  |         1439 |
  69. |           | Total:      |        22039 |
  70. | 33        | 2017-08-13  |         5530 |
  71. | 33        | 2017-08-14  |         4236 |
  72. |           | Total:      |         9766 |
  73. |           | GrandTotal: |        49065 |
  74. +-----------+-------------+--------------+
  75.  
  76.  
  77. SELECT
  78.   IF(d.minggu_ke IS NULL,'',d.minggu_ke)  AS minggu_ke,
  79.   IF(COALESCE(a.minggu_ke,'GrandTotal')='GrandTotal','GrandTotal:',COALESCE(a.tanggal,'Total:')) AS tanggal,
  80.   SUM(a.output_press) AS output_press
  81. FROM data a
  82.   LEFT JOIN (
  83.     SELECT  b. *
  84.     FROM data b
  85.     LEFT JOIN data c ON (
  86.       b.minggu_ke = c.minggu_ke
  87.       AND b.id > c.id
  88.     )
  89.     WHERE c.id IS NULL
  90. )d ON (d.minggu_ke=a.minggu_ke AND d.tanggal=a.tanggal)
  91. GROUP BY
  92.   a.minggu_ke,a.tanggal WITH ROLLUP;
  93.  
  94. +-----------+-------------+--------------+
  95. | minggu_ke | tanggal     | output_press |
  96. +-----------+-------------+--------------+
  97. | 31        | 2017-08-01  |         2631 |
  98. |           | 2017-08-02  |         3043 |
  99. |           | 2017-08-03  |         4724 |
  100. |           | 2017-08-04  |         1931 |
  101. |           | 2017-08-05  |         4931 |
  102. |           | Total:      |        17260 |
  103. | 32        | 2017-08-06  |         5233 |
  104. |           | 2017-08-07  |         2935 |
  105. |           | 2017-08-08  |         1941 |
  106. |           | 2017-08-09  |         3531 |
  107. |           | 2017-08-10  |         3982 |
  108. |           | 2017-08-11  |         2978 |
  109. |           | 2017-08-12  |         1439 |
  110. |           | Total:      |        22039 |
  111. | 33        | 2017-08-13  |         5530 |
  112. |           | 2017-08-14  |         4236 |
  113. |           | Total:      |         9766 |
  114. |           | GrandTotal: |        49065 |
  115. +-----------+-------------+--------------+
Advertisement
Add Comment
Please, Sign In to add comment