belajar pivot table

DROP TABLE IF EXISTS guru;
CREATE TABLE IF NOT EXISTS guru (
  id int(11) NOT NULL AUTO_INCREMENT,
  nama varchar(50) NOT NULL,
  nip varchar(20) NOT NULL,
  jenis_kelamin enum('L','P') NOT NULL,
  status_kepegawaian enum('PNS','NON PNS') NOT NULL,
  golongan enum('I/a','I/b','I/c','I/d','II/a','II/b','II/c','II/d','III/a','III/b','III/c','III/d','IV/a','IV/b','IV/c','IV/d') NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


INSERT INTO guru (nama, nip, jenis_kelamin, status_kepegawaian, golongan) VALUES
('Ahmad Sufandi', '19800627200408003000', 'L', 'PNS', 'III/c'),
('Suratin', '19671225199101003001', 'L', 'PNS', 'III/c'),
('Ahmad Sufandi', '198006272004080301', 'L', 'PNS', 'III/c'),
('Suratin', '196712251991010301', 'L', 'PNS', 'III/c');

SELECT * FROM guru;
+----+---------------+----------------------+---------------+--------------------+----------+
| id | nama          | nip                  | jenis_kelamin | status_kepegawaian | golongan |
+----+---------------+----------------------+---------------+--------------------+----------+
|  1 | Ahmad Sufandi | 19800627200408003000 | L             | PNS | III/c    |
|  2 | Suratin       | 19671225199101003001 | L             | PNS | III/c    |
|  3 | Ahmad Sufandi | 198006272004080301   | L             | PNS | III/c    |
|  4 | Suratin       | 196712251991010301   | L             | PNS | III/c    |
+----+---------------+----------------------+---------------+--------------------+----------+


SELECT
golongan,
SUM(IF(jenis_kelamin='L',1,0)) AS laki_laki,
SUM(IF(jenis_kelamin='P',1,0)) AS perempuan,
COUNT(*) AS jumlah
FROM
guru
GROUP BY golongan;

+----------+-----------+-----------+--------+
| golongan | laki_laki | perempuan | jumlah |
+----------+-----------+-----------+--------+
| III/c    | 4         | 0         |      4 |
+----------+-----------+-----------+--------+