Tolong pilih kategori sesuai, jenis posting (pertanyaan atau bukan) dan sertakan tag/topik yang sesuai misal komputer, php, mysql, dll.
Promosi atau posting tidak pada tempatnya akan kami hapus.
Klik link berikut untuk informasi cara menyisipkan kode program supaya tampil rapi dan terformat dengan baik di diskusiweb.com: http://www.diskusiweb.com/discussion/50415/cara-menyisipkan-kode-program-di-diskusiweb-com

Baca cara posting gambar/image di post Anda: http://www.diskusiweb.com/discussion/47345/cara-menyisipkan-menyertakan-image-pada-posting/p1

Membuat index table MySQL dengan konsep query sub query

Permisi master, mohon pencerahan

Jika kita ingin membuat index table untuk menampilkan data seperti :

SELECT mhsTahun,id_fak, mhsProdiKode, JUM_LULUS_REG, JUM_LULUS_TRANS, MAKS_IPK, MIN_IPK, RATA_IPK,
((IPKKUR/(JUM_LULUS_REG+JUM_LULUS_TRANS))*100) as 'PERS_275',
((IPKAN/(JUM_LULUS_REG+JUM_LULUS_TRANS))*100) as 'PERS_275_350',
((IPKLEH/(JUM_LULUS_REG+JUM_LULUS_TRANS))*100) as 'PERS_350'
FROM
(
SELECT mhsTahun,id_fak, mhsProdiKode
, COUNT(IF(mhsJlrrKode NOT IN ('SAJ','SAP'),mhsNiu , NULL)) AS 'JUM_LULUS_REG'
, COUNT(IF(mhsJlrrKode IN ('SAJ','SAP'), mhsNiu, NULL)) AS 'JUM_LULUS_TRANS'
, MAX(mhsIPKLulus) AS 'MAKS_IPK'
, MIN(mhsIPKLulus) AS 'MIN_IPK'
, AVG(mhsIPKLulus) AS 'RATA_IPK'
, COUNT(IF(mhsIPKLulus <'2.75',mhsNiu , NULL)) AS 'IPKKUR'
, COUNT(IF(mhsIPKLulus between '2.75' and '3.50',mhsNiu , NULL)) AS 'IPKAN'
, COUNT(IF(mhsIPKLulus >'3.50',mhsNiu , NULL)) AS 'IPKLEH'
FROM mahasiswa where mhsTahun between (DATE_FORMAT(NOW(), '%Y'))-6 and (DATE_FORMAT(NOW(), '%Y'))

group by mhsProdiKode, mhsTahun
)jm_lulus

bagaimana ya master?
Kalo yang select sederhana bisa, tapi bagaimana jika seperti query diatas?

Terima kasih dan mohon pencerahan

Comments

  • itu kenapa mesti pake sub query ?

    emang kalo query kaya begini hasilnya beda kah ?
    SELECT 
    mhsTahun,
    id_fak,
    mhsProdiKode,

    SUM(IF(mhsJlrrKode NOT IN ('SAJ','SAP'),1,0)) AS 'JUM_LULUS_REG',
    SUM(IF(mhsJlrrKode IN ('SAJ','SAP'),1,0)) AS 'JUM_LULUS_TRANS',

    MAX(mhsIPKLulus) AS 'MAKS_IPK',
    MIN(mhsIPKLulus) AS 'MIN_IPK',
    AVG(mhsIPKLulus) AS 'RATA_IPK',

    SUM(IF(mhsIPKLulus <'2.75',1,0))/COUNT(mhsJlrrKode) AS 'PERS_275',
    SUM(IF(mhsIPKLulus BETWEEN '2.75' AND '3.50',1,0))/COUNT(mhsJlrrKode) AS 'PERS_275_350',
    SUM(IF(mhsIPKLulus >'3.50',1,0))/COUNT(mhsJlrrKode) AS 'PERS_350'

    FROM
    mahasiswa
    WHERE
    mhsTahun BETWEEN (DATE_FORMAT(NOW(), '%Y'))-6 AND (DATE_FORMAT(NOW(), '%Y'))
    GROUP BY
    mhsProdiKode, mhsTahun
  • edited January 24
    Sama sih master

    Lalu kalo seperti diatas jika dibuat index table bagaimana ya master?
  • edited January 25
    hasilnya bener-bener sama ?

    kalo hasilnya sama, ya cukup pake query kaya gitu, ndak perlu sub query.

    sekarang masalah "index table" yg dimaksud itu yg bagaimana ?
    https://dev.mysql.com/doc/refman/5.7/en/create-index.html
    yg kaya gitu ?

    kalo iya ...
    yg gw tunjukin kan cuma pake 1 table doang.
    ya create index nya cuma di table mahasiswa.
Sign In or Register to comment.