Open in app

Sign in

Write

Sign in

Fundamental SQL Using FUNCTION and GROUP BY

Wulan Sopiani
9 min readFeb 12, 2021

Hai, kali ini aku akan membahas mengenai Fundamental SQL Using FUNCTION and GROUP BY yang merupakan bagian modul dari Data Analyst Career Track DQLab.

Fungsi Scalar vs Fungsi Aggregate

Fungsi adalah metode yang digunakan untuk melakukan operasi data di database. Operasi ini bisa berupa kalkulasi numerik seperti sum, count, avg, etc; atau operasi non-numerik seperti string concatenations dan sub-strings. SQL Function dapat dibagi ke dalam 2 kategori, yaitu fungsi scalar dan fungsi aggregate.

Fungsi skalar dalam SQL digunakan untuk mengembalikan nilai tunggal (single value) dari suatu nilai input yang diberikan, sedangkan fungsi agregat dalam SQL digunakan untuk melakukan perhitungan pada sekelompok nilai dan kemudian mengembalikan nilai tunggal.

Fungsi Skalar Matematika

Fungsi skalar pertama yang akan kita bahas adalah fungsi skalar untuk numerik value. Fungsi ini umumnya digunakan jika kita ingin melakukan operasi matematika di SQL secara cepat dan efektif. Di SQL sendiri ada banyak fungsi matematika.

Untuk mengecek fungsi-fungsi apa saja yang bisa dilakukan di SQL, kita bisa membuka dokumentasi fungsi SQL di sini: https://www.postgresql.org/docs/9.5/functions-math.html, untuk postgresql database dan di sini: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html, untuk mysql database.

Beberapa fungsi umumnya digunakan dapat dilihat pada tabel ini,

Untuk memudahkan pemahaman, di sini terdapat tabel dummy berisi nilai siswa semester 1 dan 2 di suatu sekolah. Berikut contoh penggunaan fungsi skalar dengan menggunakan tabel dummy:

Tabel: students

Fungsi Skalar Matematika — ABS()

Fungsi ABS( )

Syntax:

SELECT ABS(ColumnName)  
FROM TableName;

Contoh penggunaan ABS

output:

Fungsi Skalar Matematika — CEILING()

Fungsi CEILING()

Syntax:

SELECT CEILING(ColumnName) 
FROM TableName;

Contoh penggunaan CEILING()

SELECT StudentID, FirstName, LastName, CEILING(Semester1) as Semester1, CEILING(Semester2) as Semester2, MarkGrowth
FROM students;

Output:

Fungsi Skalar Matematika — FLOOR()

Fungsi FLOOR()

Syntax:

SELECT FLOOR(ColumnName)  
FROM TableName;

Contoh:

SELECT StudentID, FirstName, LastName, FLOOR(Semester1) as Semester1, FLOOR(Semester2) as Semester2, MarkGrowth
FROM students;

output:

Fungsi Skalar Matematika — ROUND()

Fungsi ROUND()

Syntax:

SELECT ROUND(ColumnName)  
FROM TableName;

contoh:

SELECT StudentID, FirstName, LastName, ROUND(Semester1,1) as Semester1, ROUND(Semester1,0) as Semester2, MarkGrowth
FROM students;

output:

Fungsi Skalar Matematika — SQRT( )

Fungsi SQRT()

Syntax:

SELECT SQRT(ColumnName)  
FROM TableName;

Contoh:

SELECT StudentID, FirstName, LastName, SQRT(Semester1) as Semester1, Semester2, MarkGrowth
FROM students;

output:

Tugas Praktek

Gunakan fungsi MOD() untuk menghitung nilai sisa jika nilai Semester1 dibagi 2 dan fungsi EXP() untuk menghitung nilai eksponensial dari nilai MarkGrowth. Gunakan kedua fungsi tersebut dalam satu SELECT-Statement.

SELECT StudentID, FirstName, LastName, MOD(Semester1,2) as Semester1, Semester2, EXP(MarkGrowth)
FROM students;

output:

Fungsi Text

Sekarang kita akan membahas tentang fungsi skalar untuk text/string value. Fungsi ini digunakan jika kita ingin melakukan operasi pada text atau karakter di SQL, misalnya, mengubah huruf kecil ke huruf besar, menghitung jumlah karakter dari text, dll. Fungsi skalar text di SQL juga cukup banyak.

Berikut merupakan tabel fungsi text pada SQL:

Fungsi Text — CONCAT( )

Fungsi CONCAT()

Syntax:

SELECT CONCAT(ColumnName1, ColumnName2, ColumnNameN)  
FROM TableName;

Contoh:

SELECT StudentID, CONCAT(FirstName, LastName) as Name, Semester1, Semester2, MarkGrowth
FROM students;

output:

Fungsi Text — SUBSTRING_INDEX( )

Fungsi Text SUBSTRING_INDEX()

Syntax:

SELECT SUBSTRING_INDEX(column, delimiter, index to return)  
FROM TableName;

Keterangan:

column → merupakan nama kolom yang akan dipecah text-nya,

delimiter → karakter atau gabungan beberapa karakter untuk pemecah text pada kolom bersangkutan,

index_to_return → indeks dari pecahan text yang akan diambil.

Contoh:

SELECT StudentID, SUBSTRING_INDEX (Email,'@',1) as Name
FROM students;

output:

Fungsi Text — SUBSTR( )

Fungsi Text SUBSTR()

Syntax:

SELECT SUBSTR(columnName, Start Index, Number of string to be extract)
FROM TableName;

Keterangan:

columnName → nama kolom yang akan dicari substring-nya

Start Index → indeks dari text yang dimiliki (dimulai dari 1)

Number of string to be extract → jumlah karakter atau beberapa karakter yang akan diambil.

Contoh:

SELECT StudentID, SUBSTR(FirstName,2,3) as initial
FROM students;

output:

Fungsi Text — LENGTH( )

Fungsi Text LENGTH()

Syntax:

SELECT LENGTH(ColumnName)
FROM TableName;

Contoh:

SELECT StudentID, FirstName, LENGTH(FirstName) as Total_Char
FROM students;

output:

Fungsi Text — REPLACE( )

Fungsi Text REPLACE()

Syntax:

SELECT REPLACE(ColumnName, Character/String to be change, New String/Character)
FROM TableName;

Keterangan:

ColumnName → nama kolom yang akan diganti isi tiap record/barisnya berdasarkan string/karakter tertentu

Character/String to be change → string/karakter yang dimiliki untuk diganti

New String/Character → string/karakter baru pengganti string/karakter sebelumnya

Contoh:

SELECT StudentID, Email, REPLACE(Email,'yahoo','gmail') as New_Email
FROM students;

output:

Tugas Praktek

Gunakan fungsi UPPER() untuk mengubah kolom FirstName menjadi seluruhnya kapital dan gunakan LOWER() untuk mengubah kolom LastName menjadi seluruhnya non-kapital. Gunakan kedua fungsi tersebut dalam satu SELECT-Statement.

SELECT StudentID, UPPER(FirstName) as FirstName, LOWER(LastName) as LastName
FROM students;

Fungsi Aggregate

Selanjutnya fungsi aggregate, fungsi aggregate ini digunakan untuk melakukan perhitungan pada sekelompok nilai. Berikut merupakan tabel fungsi aggregate pada SQL:

Fungsi Aggregate — SUM()

Fungsi Aggregate SUM()

Syntax:

SELECT SUM(ColumnName)  
FROM TableName;

Contoh:

SELECT SUM(Semester1) as Total_1, SUM(Semester2) as Total_2
FROM students;

output:

Fungsi Aggregate — COUNT()

Fungsi Aggregate COUNT()

Syntax:

SELECT COUNT(ColumnName)  
FROM TableName;

Contoh:

SELECT COUNT(FirstName) as Total_Student
FROM students;

output:

Fungsi Aggregate — AVG( )

Fungsi Aggregate AVG()

Syntax:

SELECT AVG(ColumnName)  
FROM TableName;

Contoh:

SELECT AVG(Semester1) as AVG_1, AVG(Semester2) as AVG_2
FROM students;

output:

Tugas Praktek

Setelah memahami fungsi-fungsi sebelumnya, kali ini kita diminta untuk untuk menggunakan fungsi MIN() dan MAX() untuk menghitung nilai dari kolom Semester1 dan Semester2. Di sini kita akan menggunakan fungsi tersebut dalam satu SELECT-Statement.

Dan berikut merupakan jawabannya

SELECT MIN(Semester1) as Min1, Max(Semester1) as Max1, MIN(Semester2) as Min2, Max(Semester2) as Max2 
FROM students;

output:

Fungsi Aggregate dan Group By

Jika kita akan mengolah data penjualan tahunan tentunya kita akan membutuhkan pengelompokan dan perhitungan berdasarkan penjualan setiap provinsi maupun dikelompokkan per bulan.

Untuk mengelompokkan data di SQL kita menggunakan GROUP BY Statement. GROUP BY statement akan mengelompokkan data yang bernilai sama ke dalam satu group, dan dengan menggunakan fungsi aggregate seperti (COUNT, MAX, MIN, SUM, AVG) kita bisa melakukan agregasi untuk untuk setiap group atau kelompok yang terbentuk.

Berikut merupakan penulisan sintaksnya:

Hal penting yang perlu diperhatikan adalah:

  1. GROUP BY digunakan dengan SELECT, artinya kolom yang digunakan di GROUP BY statement, juga perlu ditempatkan di SELECT.
  2. GROUP BY ditempatkan setelah WHERE, tetapi jika tidak menggunakan WHERE maka langsung ditempatkan setelah FROM.
  3. Jika menggunakan ORDER BY, maka GROUP BY ditempatkan sebelum ORDER BY.

Group by bisa dilakukan dengan single column ataupun multiple column. Contohnya:

  • Group by Single Column, data dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.
  • Group by Multiple Column, data dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.

Group by Single Column

Fungsi Group by Single Column memastikan data dapat dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.

Contoh:

SELECT province,
COUNT(DISTINCT order_id) as total_order,
SUM(item_price) as total_price
FROM sales_retail_2019
GROUP BY province;

output:

Group by Multiple Column

Dengan fungsi Group by Multiple Column, data dapat dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.

Contoh:

SELECT province,
brand,
COUNT(DISTINCT order_id) as total_order,
SUM(item_price) as total_price FROM sales_retail_2019
GROUP BY province, brand;

di sini saya hanya menampilkan menampilkan semua outputnya karena terlalu banyak :

Fungsi Aggregate dengan Grouping

Sekarang kita akan mencoba menggunakan fungsi agregasi dan GROUP BY untuk menghitung total penjualan dari setiap provinsi di tahun 2019, dan kita bandingkan dengan hasil fungsi agregasi tanpa menggunakan group by seperti pada materi sebelumnya.

Contoh aggregate dengan grouping:

SELECT province, COUNT(DISTINCT order_id) AS total_unique_order,
SUM(item_price) AS revenue FROM sales_retail_2019
GROUP BY province;

output:

Penggunaan CASE … WHEN….

Jika kita diminta untuk menambahkan kolom rekomendasi atau remark dari hasil agregasi data, misalnya nilai penjualan bulan Maret 2019 lebih dari 30M, maka diberikan rekomendasi/remark “Target Achieved”, kalau kurang diberi remark “Follow Up”. Kita bisa melakukannya dngan menggunakan CASE — statement di SQL. CASE — statement ini sama dengan IF — THEN — ELSE di pemrograman pada umumnya. Syntax dari CASE — statement seperti ini.

SELECT ColumnName1, ColumnName2,  
CASE  
    WHEN condition1 THEN result1  
    WHEN condition2 THEN result2  
    WHEN conditionN THEN resultN  
    ELSE result  
END as alias  
FROM TableName;

CASE-statement akan mengevaluasi kondisi yang sudah ditentukan, dimulai dari condition1, dan akan mengembalikan hasil (result1), jika condition1 terpenuhi (TRUE). Jika tidak, maka condition2 akan dievaluasi, dan akan mengembalikan result2 jika condition2 terpenuhi, dst. Apabila tidak ada kondisi yang terpenuhi, maka result pada bagian ELSE yang akan dikembalikan.

Tugas Praktek

Dengan menggunakan data sales_retail_2019, buatlah syntax query yang menggunakan fungsi skalar MONTH() untuk mengubah order_date dari tanggal ke bulan, fungsi aggregate SUM() untuk menjumlahkan kolom item_price.

Tambahkan kolom remark menggunakan CASE… WHEN… statement. Jika sum(item_price) >= 30.000.000.000, maka remark-nya “Target Achieved”; Jika sum(item_price) <= 25.000.000.000 maka remark-nya “Less performed”; Selain itu, beri remark “Follow Up”.

Penyelesaian:

SELECT MONTH(order_date) AS order_month, SUM(item_price) AS total_price, 
CASE  
    WHEN sum(item_price) >= 30000000000 THEN 'Target Achieved'
    WHEN sum(item_price) <= 25000000000 THEN 'Less performed'
    ELSE 'Follow Up'
END as remark
FROM sales_retail_2019
GROUP BY MONTH(order_date);

output:

Mini Project

Proyek Pekerjaan — Analisis Penjualan Part 1

Pada project ini kita akan melakukan analisis penjualan di suatu store. Adapun laporan yang diminta sebagai berikut:

  1. Total jumlah seluruh penjualan (total/revenue).
  2. Total quantity seluruh produk yang terjual.
  3. Total quantity dan total revenue untuk setiap kode produk.

Penyelesaian:

-- 1. Total jumlah seluruh penjualan (total/revenue).
SELECT SUM(total) as total 
FROM  tr_penjualan;
-- 2. Total quantity seluruh produk yang terjual.
SELECT SUM(qty) as qty 
FROM tr_penjualan;
-- 3. Total quantity dan total revenue untuk setiap kode produk.
SELECT kode_produk, SUM(qty) as qty, SUM(total) as total 
FROM tr_penjualan
GROUP BY kode_produk;

output:

Proyek Pekerjaan — Analisis Penjualan Part 2

(lanjutan …)

4. Rata — Rata total belanja per kode pelanggan.

5. Selain itu, jangan lupa untuk menambahkan kolom baru dengan nama ‘kategori’ yang mengkategorikan total/revenue ke dalam 3 kategori: High: > 300K; Medium: 100K — 300K; Low: <100K.

Penyelesaian:

-- 4. Rata - Rata total belanja per kode pelanggan.
SELECT kode_pelanggan, AVG(total) as avg_total 
FROM  tr_penjualan
GROUP BY kode_pelanggan;
/* 5. Selain itu,  jangan lupa untuk menambahkan kolom baru 
dengan nama ‘kategori’ yang mengkategorikan total/revenue ke dalam 
3 kategori: High: > 300K; Medium: 100K - 300K; Low: <100K. */
SELECT kode_transaksi,kode_pelanggan,no_urut,kode_produk, nama_produk, qty, total,
CASE  
    WHEN total > 300000 THEN 'High'
    WHEN total < 100000 THEN 'Low' 
    ELSE 'Medium'  
END as kategori
FROM tr_penjualan;

output:

Mentor: Trisna Yulia Junita (Data Scientist, PT. BUMA)

Referensi: https://academy.dqlab.id/

--

--

Wulan Sopiani

Written by Wulan Sopiani

0 Followers

Help

Status

About

Careers

Blog

Privacy

Terms

Text to speech

Teams