Ahmad Mazlan Ahmad Mazlan - 29 days ago 16
MySQL Question

SELECT field GROUP BY max(field) and GROUP BY other field MySQL

I have record like this pic, PLEASE CLICK TO SHOW PIC

how to get like this pic, PLEASE CLICK TO SHOW PIC

create table query:


create table lap ( no_faktur varchar(30) primary key, tgl_jual date,
kd_barang varchar(20), nm_barang varchar(100), harga_beli int (20),
harga_jual int(20), jum_jual int(10) );


insert table query:


insert into lap values ('FJ-2013001','2013-10-01','B-001','laptop
Apple','500000','700000','2'); insert into lap values
('FJ-2013002','2012-10-01','B-002','laptop
Apple','300000','500000','4'); insert into lap values
('FJ-2013003','2013-10-03','B-001','laptop
Apple','500000','700000','1'); insert into lap values
('FJ-2013004','2013-10-11','B-003','HDD 500
GB','700000','800000','1'); insert into lap values
('FJ-2013005','2012-10-01','B-001','laptop
Apple','500000','700000','3'); insert into lap values
('FJ-2013006','2013-10-04','B-004','MONITOR
14"','100000','800000','2'); insert into lap values
('FJ-2013007','2013-02-01','B-001','laptop
Apple','700000','850000','4'); insert into lap values
('FJ-2013008','2013-10-21','B-005','HDD 500
GB','800000','900000','3'); insert into lap values
('FJ-2013009','2013-10-27','B-001','laptop
Apple','500000','700000','1');


I think like this:


select
no_faktur,tgl_jual,kd_barang,nm_barang,harga_jual,jum_jual,sum(harga_jual*jum_jual) as total_jual from lap where month(tgl_jual)='10' and
year(tgl_jual)='2013' and jum_jual=(select max(jum_jual) from lap
group by kd_barang) order by total_jual desc;


but doesnt work because *jum_jual more than 1 rows.

Please help, thanks before

Answer

select no_faktur,tgl_jual,kd_barang,nm_barang,harga_jual,sum(jum_jual) as jum_jual,sum(harga_jual*jum_jual) as total_jual from lap where month(tgl_jual)='10' and year(tgl_jual)='2013' group by kd_barang order by total_jual desc;