.::: Sample min, max, avg, sum, count, Group by, order by, Having, where, comparison (=,<>,>,<,>= atau <=), Between, In, like / not like on Grouping, Sorting & Criteria Data SQL :::.

1. pengelompokan data (group by) dan aggregation function
select * from PASOK;
select KODE_BARANG as KD_BRG, min(JUMLAH_PASOK) as MINIMUM_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, max(JUMLAH_PASOK) as MAXIMUM_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, avg(JUMLAH_PASOK) as AVERAGE_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, sum(JUMLAH_PASOK) as JUMLAH_PASOK from PASOK  group by KODE_BARANG;
select KODE_BARANG as KD_BRG, count(JUMLAH_PASOK) as COUNT_PASOK from PASOK group by KODE_BARANG;

2. Pengurutan data (order by)
select * from BARANG;
select * from BARANG order by NAMA_BARANG ;
select * from BARANG order by STOK_BARANG ;
select * from BARANG order by STOK_BARANG asc;
select * from BARANG order by STOK_BARANG desc;

3. kriteria data where with comparison (=,<>,>,<,>= atau <=), having count
kriteria data adalah data yang akan ditampilkan merupakan data yang memenuhi kriteria yang ada dan ditentukan
select * from PASOK;
-- EJ-01, EJ-02, EB-02 = 3
select KODE_SUPLIER, count(JUMLAH_PASOK) as CACAH_PASOK from PASOK group by KODE_SUPLIER having count(*)=3;
-- EJ-01(8+2+5+2+3=20), EB-01(4+3+12=19)
select KODE_SUPLIER, sum(JUMLAH_PASOK) as SUM_PASOK from PASOK group by KODE_SUPLIER having sum(JUMLAH_PASOK)>15;

4. kriteria data where with "Between"
select * from PASOK;
select * from PASOK where JUMLAH_PASOK between 5 and 12 order by JUMLAH_PASOK;

5. kriteria data where with "In"
select * from PASOK;
select * from PASOK where JUMLAH_PASOK in(2,8,12);
select * from PASOK where JUMLAH_PASOK in(2,8,100);

6. like / not like
select * from barang;
select * from barang where NAMA_BARANG like 'R%';
select * from barang where NAMA_BARANG not like 'R%';

http://teguhth.blogspot.com/2013/09/how-to-using-min-max-avg-sum-count.html