Hallo! Selamat Datang di Marketplace produk digital, Freelancer terbaik di indonesia (67478 Members, 783 Products, 5290 Topic, 12955 Comments)

Sql Query untuk menghitung berapa kali dosen menguji mahasiswa?

Dibuat
Login Terakhir 7 Bulan lalu,
Telah Dilihat 1152 Kali
Harry Cool Mengatakan : Sultan Account 
  1. Assalamualaikum Warahmatullahi Wabarakatuh...
  2.  
  3. Maaf mengganggu mas, please help to solve!!!
  4.  
  5. t_dosen
  6. ---------------------------------------
  7. DosenID     NamaDosen
  8. ---------------------------------------
  9. 101             Doni         
  10. 102             Sinta
  11. 103             Teguh
  12. ----------------------------------------
  13.  
  14. t_jadwal
  15. -----------------------------------------------------
  16. JadwalID   Penguji1   Penguji2     nim
  17. -----------------------------------------------------
  18. 1                  101            102            203
  19. 2                  102            103            204
  20. 3                  103            102            205
  21. -----------------------------------------------------
  22.  
  23. Output Jumlah Berapa kali Menguji
  24. ---------------------------------------------------
  25. DosenID   JumlahMenguji
  26. ---------------------------------------------------
  27. 101            1 kali      
  28. 102            3 kali
  29. 103            2 kali
  30. ---------------------------------------
  31.  
  32. Note:
  33. 1. Andaikan Penguji1 dan Penguji2 satu kolom bisa tapi kalau kondisinya 2 kolom gmana ya, langsung query sql agak ribet...
  34.  
  35. 2. Mas Roby boleh bantu query sql count nya... ?
  36. TerimaKasih

Ada 5 Jawaban

PHPMU.COM Support
commented on 14 Agu 2018 06:30:12		 
  1. Assalam,.. coba begini mas :
  2. SELECT a.*, (IFNULL(b.jum1, 0)+IFNULL(c.jum2, 0)) as total FROM
  3. (SELECT * FROM t_dosen) as a LEFT JOIN
  4. (SELECT Penguji1, COUNT(*) as jum1 FROM t_jadwal GROUP BY Penguji1 HAVING COUNT(Penguji1)) as b on a.DosenID=b.Penguji1
  5. LEFT JOIN
  6. (SELECT Penguji2, COUNT(*) as jum2 FROM t_jadwal GROUP BY Penguji2 HAVING COUNT(Penguji2)) as c on a.DosenID=c.Penguji2
Harry Cool
commented on 14 Agu 2018 07:10:05		 
  1. Assalam,.. terimakasih mas roby
Harry Cool
commented on 14 Agu 2018 12:27:01		 
  1.     Assalam,.. pada tabel t_jadwal ditambahkan TahunID sehingga menjadi
  2.  
  3.     SELECT a.*, (IFNULL(b.jum1, 0)+IFNULL(c.jum2, 0)) as total FROM
  4.     (SELECT * FROM t_dosen) as a LEFT JOIN
  5.     (SELECT Penguji1, COUNT(*) as jum1,b.TahunID FROM t_jadwal GROUP BY Penguji1 HAVING COUNT(Penguji1)) as b on a.DosenID=b.Penguji1
  6.     LEFT JOIN
  7.     (SELECT Penguji2, COUNT(*) as jum2FROM t_jadwal GROUP BY Penguji2 HAVING COUNT(Penguji2)) as c on a.DosenID=c.Penguji2
  8.     where b.TahunID='20162'
  9.  
  10.     nah error dech.... hehe
PHPMU.COM Support
commented on 14 Agu 2018 19:27:36		 
  1. Assalam,.. jika mau ditambahkan kondisi bukan disana mas,..
  2. Coba pahami query yang saya share di atas, berarti untuk tahun di table t_jadwal ya??
  3. jadinya begini jika ditambahkan kondisi :
  4.  
  5. SELECT a.*, (IFNULL(b.jum1, 0)+IFNULL(c.jum2, 0)) as total FROM
  6. (SELECT * FROM t_dosen) as a LEFT JOIN
  7. (SELECT Penguji1, COUNT(*) as jum1 FROM t_jadwal WHERE tahun='20162' GROUP BY Penguji1 HAVING COUNT(Penguji1)) as b on a.DosenID=b.Penguji1
  8. LEFT JOIN
  9. (SELECT Penguji2, COUNT(*) as jum2 FROM t_jadwal WHERE tahun='20162' GROUP BY Penguji2 HAVING COUNT(Penguji2)) as c on a.DosenID=c.Penguji2
Harry Cool
commented on 14 Agu 2018 21:15:14		 
  1. Assalam,.. oya mas terimakasih banyak..
Maaf, Untuk Memberikan Komentar Anda Harus Login !!!

CV Asktech Indonesia

Cari Produk Digital, Freelancer Terbaik dan Terpercaya di Indonesia

CV Asktech Indonesia
Jl. Angkasa Puri, Perundam V, Tunggul Hitam
Padang 25176 - Indonesia

Telp./Fax. (0751) 461695
Phone/WA. +62-812-6777-1344
E-mail robby.prihandaya@gmail.com

Tentang Kami

Jam Layanan

  • Senin - Jum'at: 09:00 - 17:00 WIB
  • Sabtu - Minggu: 09:00 - 15:00 WIB

Knowledgebase