query MAX() of SUM() data

USE test;

DROP TABLE IF EXISTS penjualan;
CREATE TABLE IF NOT EXISTS penjualan(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  code CHAR(5) NOT NULL,
  waktu DATETIME NOT NULL,
  jumlah INT(11),
  PRIMARY KEY(id)
)ENGINE=MyISAM;

INSERT INTO penjualan(code,waktu,jumlah)
VALUES
('A001','2014-12-01 12:34:56',4),
('A002','2014-12-01 13:03:33',5),
('A001','2014-12-01 14:41:21',2),
('A002','2014-12-01 14:44:12',4),
('A003','2014-12-01 16:10:09',1),
('A002','2014-12-02 09:14:55',8),
('A001','2014-12-02 11:04:15',3),
('A003','2014-12-02 12:23:34',17),
('A002','2014-12-02 15:29:16',4),
('A001','2014-12-03 08:24:26',4),
('A002','2014-12-03 08:34:43',8),
('A003','2014-12-03 09:01:51',2),
('A001','2014-12-03 10:31:36',1),
('A002','2014-12-03 16:59:02',3);

-- dengan query sederhana diperoleh:

SELECT * FROM penjualan;
+----+------+---------------------+--------+
| id | code | waktu               | jumlah |
+----+------+---------------------+--------+
|  1 | A001 | 2014-12-01 12:34:56 |      4 |
|  2 | A002 | 2014-12-01 13:03:33 |      5 |
|  3 | A001 | 2014-12-01 14:41:21 |      2 |
|  4 | A002 | 2014-12-01 14:44:12 |      4 |
|  5 | A003 | 2014-12-01 16:10:09 |      1 |
|  6 | A002 | 2014-12-02 09:14:55 |      8 |
|  7 | A001 | 2014-12-02 11:04:15 |      3 |
|  8 | A003 | 2014-12-02 12:23:34 |     17 |
|  9 | A002 | 2014-12-02 15:29:16 |      4 |
| 10 | A001 | 2014-12-03 08:24:26 |      4 |
| 11 | A002 | 2014-12-03 08:34:43 |      8 |
| 12 | A003 | 2014-12-03 09:01:51 |      2 |
| 13 | A001 | 2014-12-03 10:31:36 |      1 |
| 14 | A002 | 2014-12-03 16:59:02 |      3 |
+----+------+---------------------+--------+
14 rows in set (0.04 sec)

-- [A] kalo pengin liat jumlah total perhari dari masing-masing code bisa pake query:

SELECT code,DATE_FORMAT(waktu,'%d-%m-%Y') AS tgl,SUM(jumlah) AS jumlah
FROM penjualan
GROUP BY DATE_FORMAT(waktu,'%Y%m%d'),code;

+------+------------+--------+
| code | tgl        | jumlah |
+------+------------+--------+
| A001 | 01-12-2014 |      6 |
| A002 | 01-12-2014 |      9 |
| A003 | 01-12-2014 |      1 |
| A001 | 02-12-2014 |      3 |
| A002 | 02-12-2014 |     12 |
| A003 | 02-12-2014 |     17 |
| A001 | 03-12-2014 |      5 |
| A002 | 03-12-2014 |     11 |
| A003 | 03-12-2014 |      2 |
+------+------------+--------+
9 rows in set (0.04 sec)

-- [B] kalo pengin liat jumlah total terbesar pertanggal dan code bisa dengan query:

SELECT c.tgl,MAX(c.jumlah) AS jumlah
FROM
  (
    SELECT DATE_FORMAT(waktu,'%d-%m-%Y') AS tgl,SUM(jumlah) AS jumlah
    FROM penjualan
    GROUP BY DATE_FORMAT(waktu,'%Y%m%d'),code
  ) c
GROUP BY b.tgl;

+------------+--------+
| tgl        | jumlah |
+------------+--------+
| 01-12-2014 |      9 |
| 02-12-2014 |     17 |
| 03-12-2014 |     11 |
+------------+--------+
3 rows in set (0.02 sec)


-- nah dari [A] dan [B] kalo pengin tahu jumlah total terbesar pertanggal dengan menampilkan codenya juga bisa dibuat query sebagai berikut:

SELECT a.code,a.tgl,a.jumlah
FROM
  (
    SELECT code,DATE_FORMAT(waktu,'%d-%m-%Y') AS tgl,SUM(jumlah) AS jumlah
    FROM penjualan
    GROUP BY DATE_FORMAT(waktu,'%Y%m%d'),code
  ) a
JOIN
  (
    SELECT b.tgl,MAX(b.jumlah) AS jumlah
    FROM
    (
      SELECT DATE_FORMAT(waktu,'%d-%m-%Y') AS tgl,SUM(jumlah) AS jumlah
      FROM penjualan
      GROUP BY DATE_FORMAT(waktu,'%Y%m%d'),code
    ) b
    GROUP BY b.tgl
  ) c
ON (a.tgl=c.tgl AND a.jumlah=c.jumlah);

+------+------------+--------+
| code | tgl        | jumlah |
+------+------------+--------+
| A002 | 01-12-2014 |      9 |
| A003 | 02-12-2014 |     17 |
| A002 | 03-12-2014 |     11 |
+------+------------+--------+
3 rows in set (0.03 sec)