CODEWITHSUNDEEP
mysqlsql
user3805059
user3805059

Reputation: 21

How to display the maximum value in sql with 2 categories

I have a table that looks like this at the moment:

Table siswa:

create table siswa(nis char(4) primary key, nama char(20), jk char(1), tmpt_lahir char(10), tgl_lahir date, nm_wali char(20), alamat char(50));

Insert Into table siswa:

insert into siswa values('0001', 'budi', 'L', 'sekayu', '1994-05-30', 'yanto', 'kayuara'), ('0002', 'joko', 'L', 'lumpatan', '1995-05-28', 'usman', 'bailangu'), ('0003', 'santoso', 'L', 'kayuara', '1994-05-30', 'yanto', 'kayuara'), ('0004', 'susan', 'P', 'bailangu', '1995-03-02', 'michael', 'bailangu'), ('0005', 'marta', 'L', 'epil', '1995-05-07', 'yanto', 'sekayu'), ('0006', 'lusi', 'P', 'sekayu', '1995-03-02', 'budi', 'lumpatan'), ('0007', 'manap', 'L', 'lumpatan', '1995-05-28', 'abdul', 'kayuara'), ('0008', 'budi', 'L', 'kayuara', '1994-12-01', 'subhan', 'kayuara');

Table mata_pljran

create table mata_pljran(kd_mp char(10) primary key, nm_mp char(20), kelas char(2), semester char(10), kkm int);

Insert into table mata_pljran

insert into mata_pljran values('10.01.001', 'matematika', '10', 'ganjil', '70'), ('10.01.002', 'bahasa indonesia', '10', 'ganjil', '80'), ('10.01.003', 'kewarganegaraan', '10', 'ganjil', '80'), ('10.01.004', 'agama', '10', 'ganjil', '80'), ('10.01.005', 'bahasa inggris', '10', 'ganjil', '65'), ('10.02.001', 'fisika', '10', 'genap', '65'), ('10.02.002', 'kimia', '10', 'genap', '70'), ('10.02.003', 'biologi', '10', 'genap', '70'), ('10.02.004', 'matematika', '10', 'genap', '70');

Table nilai

create table nilai( nis char(4), kd_mp char(10) , nk decimal(5,2), nuh decimal(5,2), nuts decimal(5,2), nuas decimal(5,2), akhir decimal(5,2) default null, primary key (nis,kd_mp), foreign key (nis) references siswa(nis) on update cascade on delete cascade, foreign key (kd_mp) references mata_pljran(kd_mp) on update cascade on delete cascade ); update nilai set akhir=nk*(0.15)+nuh*(0.15)+nuts*(0.3)+nuas*(0.4);

Insert into table nilai

INSERT INTO `nilai` (`nis`, `kd_mp`, `nk`, `nuh`, `nuts`, `nuas`) VALUES ('0001', '10.01.001', '80.00', '75.00', '85.00', '90.00'), ('0001', '10.01.002', '85.00', '85.00', '85.00', '85.00'), ('0001', '10.01.003', '75.00', '90.00', '85.00', '85.00'), ('0001', '10.01.004', '90.00', '65.00', '85.00', '85.00'), ('0001', '10.01.005', '95.00', '55.00', '85.00', '85.00'), ('0002', '10.01.001', '65.00', '68.00', '85.00', '85.00'), ('0002', '10.01.002', '80.00', '78.00', '85.00', '85.00'), ('0002', '10.01.003', '95.00', '100.00', '85.00', '85.00'), ('0002', '10.01.004', '85.00', '98.00', '85.00', '95.00'), ('0003', '10.01.001', '90.00', '68.00', '85.00', '95.00'), ('0003', '10.01.002', '85.00', '76.00', '35.00', '68.00'), ('0003', '10.01.003', '45.00', '87.00', '20.00', '45.00'), ('0003', '10.01.004', '35.00', '65.00', '85.00', '75.00'), ('0003', '10.01.005', '85.00', '95.00', '85.00', '85.00'), ('0005', '10.01.001', '90.00', '65.00', '85.00', '90.00'), ('0005', '10.01.002', '85.00', '78.00', '85.00', '90.00'), ('0005', '10.01.003', '95.00', '98.00', '85.00', '90.00'), ('0005', '10.01.004', '85.00', '58.00', '85.00', '75.00');

Display final value(na):

select s.nis, nama, m.kd_mp, nm_mp, kkm, (akhir) as na,
if (akhir <kkm,'GAGAL','TUNTAS') as ket from siswa s, mata_pljran m, nilai n where
s.nis=n.nis and m.kd_mp=n.kd_mp;

RESULT:

+------+---------+-----------+------------------+------+---------+--------+
| nis  | nama    | kd_mp     | nm_mp            | kkm  | na      | ket    |
+------+---------+-----------+------------------+------+---------+--------+
| 0001 | budi    | 10.01.001 | matematika       |   70 | 84.7500 | TUNTAS |
| 0002 | joko    | 10.01.001 | matematika       |   70 | 79.4500 | TUNTAS |
| 0003 | santoso | 10.01.001 | matematika       |   70 | 87.2000 | TUNTAS |
| 0005 | marta   | 10.01.001 | matematika       |   70 | 84.7500 | TUNTAS |
| 0001 | budi    | 10.01.002 | bahasa indonesia |   80 | 85.0000 | TUNTAS |
| 0002 | joko    | 10.01.002 | bahasa indonesia |   80 | 83.2000 | TUNTAS |
| 0003 | santoso | 10.01.002 | bahasa indonesia |   80 | 61.8500 | GAGAL  |
| 0005 | marta   | 10.01.002 | bahasa indonesia |   80 | 85.9500 | TUNTAS |
| 0001 | budi    | 10.01.003 | kewarganegaraan  |   80 | 84.2500 | TUNTAS |
| 0002 | joko    | 10.01.003 | kewarganegaraan  |   80 | 88.7500 | TUNTAS |
| 0003 | santoso | 10.01.003 | kewarganegaraan  |   80 | 43.8000 | GAGAL  |
| 0005 | marta   | 10.01.003 | kewarganegaraan  |   80 | 90.4500 | TUNTAS |
| 0001 | budi    | 10.01.004 | agama            |   80 | 82.7500 | TUNTAS |
| 0002 | joko    | 10.01.004 | agama            |   80 | 90.9500 | TUNTAS |
| 0003 | santoso | 10.01.004 | agama            |   80 | 70.5000 | GAGAL  |
| 0005 | marta   | 10.01.004 | agama            |   80 | 76.9500 | GAGAL  |
| 0001 | budi    | 10.01.005 | bahasa inggris   |   65 | 82.0000 | TUNTAS |
| 0003 | santoso | 10.01.005 | bahasa inggris   |   65 | 86.5000 | TUNTAS |
+------+---------+-----------+------------------+------+---------+--------+

I would to display max(na) or max(akhir) for nm_mp=matematika and bahasa inggris like this

+------+---------+-----------+------------------+------+---------+--------+
| nis  | nama    | kd_mp     | nm_mp            | kkm  | na      | ket    |
+------+---------+-----------+------------------+------+---------+--------+
| 0003 | santoso | 10.01.001 | matematika       |   70 | 87.2000 | TUNTAS |
| 0003 | santoso | 10.01.005 | bahasa inggris   |   65 | 86.5000 | TUNTAS |
+------+---------+-----------+------------------+------+---------+--------+

How can I do that in mySQL ? Many thanks!

i made it from cmd, sorry for my bad english

EDIT: FINISHED by me ^_^ :

select s.nis, nama, m.kd_mp, nm_mp, kkm, (akhir) as na,
if (akhir <kkm,'GAGAL','TUNTAS') as ket from siswa s, mata_pljran m, nilai n where
akhir=(select max(akhir)
from nilai as a where a.kd_mp=n.kd_mp
having nm_mp in('bahasa inggris' , 'matematika')) and
s.nis=n.nis and m.kd_mp=n.kd_mp;

and ther result:

+------+---------+-----------+----------------+------+-------+--------+
| nis  | nama    | kd_mp     | nm_mp          | kkm  | na    | ket    |
+------+---------+-----------+----------------+------+-------+--------+
| 0003 | santoso | 10.01.001 | matematika     |   70 | 87.20 | TUNTAS |
| 0003 | santoso | 10.01.005 | bahasa inggris |   65 | 86.50 | TUNTAS |
+------+---------+-----------+----------------+------+-------+--------+

thanks :D

Upvotes: 2

Views: 101

Answers (3)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try this:-

 SELECT NIS, NAMA, KD_MP, NM_MP, KKM, MAX(NA), KET
 FROM YOUR_TABLE
 WHERE NM_MP = 'matematika'
 OR NM_MP = 'bahasa inggris'
 GROUP BY NM_MP

This might be helpful to you.

Upvotes: 0

Gaurav Gandhi
Gaurav Gandhi

Reputation: 3201

Just a thought(hadn't tried practically)

SELECT * FROM TABLE_NAME having max(na) = na GROUP BY nm_mp

If it works or doesn't work, do tell me. Its for all category.

For just that two category, try this.

SELECT * FROM TABLE_NAME WHERE nm_mp IN('matematika', 'bahasa inggris') having max(na) = na GROUP BY nm_mp

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

In MySQL you need to do it old-school using a Correlated Subquery:

select *
from tab as t1
where nm_mp in ('matematika', 'bahasa inggris')
  and na = (select max(na) 
            from tab as t2
            where t1.nm_mp = t2.nm_mp)

or a Derived Table

select t1.*
from tab as t1 join
   (select nm_mp_, max(na) as max_na 
    from tab as t2
    where nm_mp in ('matematika', 'bahasa inggris')
    group by nm_mp
   ) as t2
on t1.nm_mp = t2.nm_mp
and t1.na = t2.max_na

Upvotes: 0

Related Questions