Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS data;
- CREATE TABLE IF NOT EXISTS data(
- id INT AUTO_INCREMENT PRIMARY KEY,
- minggu_ke INT,
- output_press FLOAT,
- tanggal DATE
- );
- INSERT INTO data(minggu_ke,output_press,tanggal)
- VALUES
- (31,2631.0,'2017-08-01'),
- (31,3043.0,'2017-08-02'),
- (31,4724.0,'2017-08-03'),
- (31,1931.0,'2017-08-04'),
- (31,4931.0,'2017-08-05'),
- (32,5233.0,'2017-08-06'),
- (32,2935.0,'2017-08-07'),
- (32,1941.0,'2017-08-08'),
- (32,3531.0,'2017-08-09'),
- (32,3982.0,'2017-08-10'),
- (32,2978.0,'2017-08-11'),
- (32,1439.0,'2017-08-12'),
- (33,5530.0,'2017-08-13'),
- (33,4236.0,'2017-08-14');
- SELECT * FROM data;
- +----+-----------+--------------+------------+
- | id | minggu_ke | output_press | tanggal |
- +----+-----------+--------------+------------+
- | 1 | 31 | 2631 | 2017-08-01 |
- | 2 | 31 | 3043 | 2017-08-02 |
- | 3 | 31 | 4724 | 2017-08-03 |
- | 4 | 31 | 1931 | 2017-08-04 |
- | 5 | 31 | 4931 | 2017-08-05 |
- | 6 | 32 | 5233 | 2017-08-06 |
- | 7 | 32 | 2935 | 2017-08-07 |
- | 8 | 32 | 1941 | 2017-08-08 |
- | 9 | 32 | 3531 | 2017-08-09 |
- | 10 | 32 | 3982 | 2017-08-10 |
- | 11 | 32 | 2978 | 2017-08-11 |
- | 12 | 32 | 1439 | 2017-08-12 |
- | 13 | 33 | 5530 | 2017-08-13 |
- | 14 | 33 | 4236 | 2017-08-14 |
- +----+-----------+--------------+------------+
- SELECT
- IF(COALESCE(tanggal,'Total')='Total','',COALESCE(minggu_ke,'GrandTotal')) AS minggu_ke,
- IF(COALESCE(minggu_ke,'GrandTotal')='GrandTotal','GrandTotal:',COALESCE(tanggal,'Total:')) AS tanggal,
- SUM(output_press) AS output_press
- FROM data
- GROUP BY minggu_ke,tanggal WITH ROLLUP;
- +-----------+-------------+--------------+
- | minggu_ke | tanggal | output_press |
- +-----------+-------------+--------------+
- | 31 | 2017-08-01 | 2631 |
- | 31 | 2017-08-02 | 3043 |
- | 31 | 2017-08-03 | 4724 |
- | 31 | 2017-08-04 | 1931 |
- | 31 | 2017-08-05 | 4931 |
- | | Total: | 17260 |
- | 32 | 2017-08-06 | 5233 |
- | 32 | 2017-08-07 | 2935 |
- | 32 | 2017-08-08 | 1941 |
- | 32 | 2017-08-09 | 3531 |
- | 32 | 2017-08-10 | 3982 |
- | 32 | 2017-08-11 | 2978 |
- | 32 | 2017-08-12 | 1439 |
- | | Total: | 22039 |
- | 33 | 2017-08-13 | 5530 |
- | 33 | 2017-08-14 | 4236 |
- | | Total: | 9766 |
- | | GrandTotal: | 49065 |
- +-----------+-------------+--------------+
- SELECT
- IF(d.minggu_ke IS NULL,'',d.minggu_ke) AS minggu_ke,
- IF(COALESCE(a.minggu_ke,'GrandTotal')='GrandTotal','GrandTotal:',COALESCE(a.tanggal,'Total:')) AS tanggal,
- SUM(a.output_press) AS output_press
- FROM data a
- LEFT JOIN (
- SELECT b. *
- FROM data b
- LEFT JOIN data c ON (
- b.minggu_ke = c.minggu_ke
- AND b.id > c.id
- )
- WHERE c.id IS NULL
- )d ON (d.minggu_ke=a.minggu_ke AND d.tanggal=a.tanggal)
- GROUP BY
- a.minggu_ke,a.tanggal WITH ROLLUP;
- +-----------+-------------+--------------+
- | minggu_ke | tanggal | output_press |
- +-----------+-------------+--------------+
- | 31 | 2017-08-01 | 2631 |
- | | 2017-08-02 | 3043 |
- | | 2017-08-03 | 4724 |
- | | 2017-08-04 | 1931 |
- | | 2017-08-05 | 4931 |
- | | Total: | 17260 |
- | 32 | 2017-08-06 | 5233 |
- | | 2017-08-07 | 2935 |
- | | 2017-08-08 | 1941 |
- | | 2017-08-09 | 3531 |
- | | 2017-08-10 | 3982 |
- | | 2017-08-11 | 2978 |
- | | 2017-08-12 | 1439 |
- | | Total: | 22039 |
- | 33 | 2017-08-13 | 5530 |
- | | 2017-08-14 | 4236 |
- | | Total: | 9766 |
- | | GrandTotal: | 49065 |
- +-----------+-------------+--------------+
Advertisement
Add Comment
Please, Sign In to add comment