Menampilkan hasil pendapatan pegawai dan per project.

project.sql

mysql> create table if not exists kontributor (id int not null auto_increment, nama varchar(45), primary key(id)); 
Query OK, 0 rows affected (0.10 sec)

mysql> create table if not exists project ( id int not null auto_increment, namaproject varchar(45), biaya int, primary key(id));
Query OK, 0 rows affected (0.46 sec)

mysql> create table if not exists relasi_kontributor ( kontributor_id int not null, project_id int not null 
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> show tables;
+--------------------+
| Tables_in_proyek   |
+--------------------+
| kontributor        |
| project            |
| relasi_kontributor |
+--------------------+
3 rows in set (0.00 sec)

mysql> insert into kontributor values (1, 'defri'), (2, 'fajar'), (3, 'utomo');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from kontributor;
+----+-------+
| id | nama  |
+----+-------+
|  1 | defri |
|  2 | fajar |
|  3 | utomo |
+----+-------+
3 rows in set (0.00 sec)

mysql> insert into project values (1, 'elearning', '2000000'), (2, 'e-ktp', '4000000');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from project;
+----+-------------+---------+
| id | namaproject | biaya   |
+----+-------------+---------+
|  1 | elearning   | 2000000 |
|  2 | e-ktp       | 4000000 |
+----+-------------+---------+
2 rows in set (0.00 sec)

mysql> insert into relasi_kontributor values (1,1), (2,1), (1,2), (2,2), (3,2);
Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from relasi_kontributor;
+----------------+------------+
| kontributor_id | project_id |
+----------------+------------+
|              1 |          1 |
|              2 |          1 |
|              1 |          2 |
|              2 |          2 |
|              3 |          2 |
+----------------+------------+
5 rows in set (0.00 sec)

mysql> select k.nama, p.namaproject, sum(p.biaya / jml_kontributor) as pendapatan from project p 
    -> inner join relasi_kontributor r on p.id = r.project_id
    -> inner join ( select project_id, count(kontributor_id) jml_kontributor from relasi_kontributor group by project_id) a on a.project_id = p.id
    -> inner join kontributor k on r.kontributor_id = k.id
    -> group by k.nama, p.namaproject;
+-------+-------------+--------------+
| nama  | namaproject | pendapatan   |
+-------+-------------+--------------+
| defri | e-ktp       | 1333333.3333 |
| defri | elearning   | 1000000.0000 |
| fajar | e-ktp       | 1333333.3333 |
| fajar | elearning   | 1000000.0000 |
| utomo | e-ktp       | 1333333.3333 |
+-------+-------------+--------------+
5 rows in set (0.00 sec)

mysql> select k.nama, sum(p.biaya * 1.0 / jml_kontributor) as pendapatan from project p
    -> inner join relasi_kontributor r on p.id = r.project_id
    -> inner join (select project_id, count(kontributor_id) jml_kontributor from relasi_kontributor group by project_id) a on a.project_id = p.id
    -> inner join kontributor k on r.kontributor_id = k.id
    -> group by k.nama;
+-------+---------------+
| nama  | pendapatan    |
+-------+---------------+
| defri | 2333333.33333 |
| fajar | 2333333.33333 |
| utomo | 1333333.33333 |
+-------+---------------+
3 rows in set (0.00 sec)

mysql>