Hai semuanya, di materi kali ini kita akan membahas tentang Jenis dari Functions yaitu Single Row Function dan Group Functions. Karena materinya akan lumayan panjang seperti biasa kita akan bagi menjadi beberapa bagian diantaranya:

  1. Using single row functions
  2. Using Group / Aggregate functions
  3. Using GROUP BY clause
  4. Using HAVING clause
  5. Different between WHERE and HAVING clause?
  6. Using GROUPING SET clause

Ok langsung aja kita bahas materi yang pertama

Using single row functions

Single row function is Functions return a single result row for every row of a queried table or view.

ilustration-single-row-func

Single row function yang kita bisa gunakan, ada banyak sekali sesuai dengan apa yang telah kita bahas di artikel sebelumnya ataupun kita juga bisa menggunakan function yang kita buat sendiri. These functions can appear in select lists, WHERE clauses, With Queries and more.

Contoh penggunaannya seperti berikut:

Jika dijalankan maka hasilnya seperti berikut:

hr=# SELECT  UPPER(last_name) nama,
hr-#         to_char(salary, '$L999,999.00') gaji_sebulan,
hr-#         concat(first_name, ' ', last_name) as nama_lengkap
hr-# FROM employees
hr-# LIMIT 10;
   nama    | gaji_sebulan  |   nama_lengkap
-----------+---------------+------------------
 KING      | $   24,000.00 | Steven King
 KOCHHAR   | $   17,000.00 | Neena Kochhar
 DE HAAN   | $   17,000.00 | Lex De Haan
 HUNOLD    | $    9,000.00 | Alexander Hunold
 ERNST     | $    6,000.00 | Bruce Ernst
 AUSTIN    | $    4,800.00 | David Austin
 PATABALLA | $    4,800.00 | Valli Pataballa
 LORENTZ   | $    4,200.00 | Diana Lorentz
 GREENBERG | $   12,000.00 | Nancy Greenberg
 FAVIET    | $    9,000.00 | Daniel Faviet
(10 rows)

Using Group / Aggregate functions

Aggregate functions compute a single result from a set of input values. Jika kita gambarkan ilustrasinya seperti berikut:

ilustration-aggregate-function

Group atau Aggregate function di bagi menjadi beberapa diantaranya:

  1. General-Purpose Aggregate Functions
  2. Aggregate Functions for Statistics
  3. Ordered-Set Aggregate Functions
  4. Hypothetical-Set Aggregate Functions
  5. Grouping Operations

Untuk general purpose aggregate function berikut adalah beberapa function yang paling umum di gunakan:

Functions Description
avg ( numeric ) → numeric Computes the average (arithmetic mean) of all the non-null input values.
bool_and ( boolean ) → boolean Returns true if all non-null input values are true, otherwise false.
bool_or ( boolean ) → boolean Returns true if any non-null input value is true, otherwise false.
count ( "any" ) → bigint Computes the number of input rows in which the input value is not null.
max ( any ) → same as input type Computes the maximum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, and arrays of any of these types.
min ( any ) → same as input type Computes the minimum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, and arrays of any of these types.
sum ( number ) → same as input type Computes the sum of the non-null input values.

Sedangkan untuk Statistic purpose berikut adalah beberapa function yang paling umum di gunakan:

Functions Description
corr ( Y double , X double ) → double Computes the correlation coefficient.
stddev ( numeric_type ) → double This is a historical alias for stddev_samp.
variance ( numeric_type ) → double This is a historical alias for var_samp.

Dan masih banyak lagi, Berikut adalah contoh penggunaanya di SQL:

Jika dijalankan hasilnya seperti berikut:

hr=# SELECT  max(salary) max_salary,
hr-#         min(salary) min_salary,
hr-#         avg(salary) avg_salary,
hr-#         count(*) count_employees
hr-# FROM employees;
 max_salary | min_salary |      avg_salary       | count_employees
------------+------------+-----------------------+-----------------
   24000.00 |    2100.00 | 6461.6822429906542056 |             107
(1 row)

Using GROUP BY clause

The GROUP BY clause is used to group together those rows in a table that have the same values in all the columns listed. Jika di gambarkan berikut ilustrasinya

ilustration-group-by

Untuk basic usage seperti berikut:

SELECT select_list
FROM ...
[WHERE ...]
GROUP BY grouping_column_reference [, grouping_column_reference]...

The effect is to combine each set of rows having common values into one group row that represents all rows in the group. Berikut adalah contoh penggunaanya di SQL:

Jika dijalankan hasilnya seperti berikut:

hr=# SELECT  job_id
hr-# FROM employees
hr-# GROUP BY job_id;
   job_id
------------
 SH_CLERK
 AD_VP
 SA_MAN
 PR_REP
 MK_REP
 AD_PRES
 FI_ACCOUNT
 AC_ACCOUNT
(19 rows)

NOTE: Grouping without aggregate expressions effectively calculates the set of distinct values in a column.

In general, if a table is grouped, columns that are not listed in GROUP BY cannot be referenced except in aggregate expressions. An example with aggregate expressions is:

Jika dijalankan hasilnya seperti berikut:

hr=# SELECT  job_id,
hr-#         count(*) count_employees_by_job,
hr-#         sum(salary) salary_group_by_job
hr-# FROM employees
hr-# GROUP BY job_id;
   job_id   | count_employees_by_job | salary_group_by_job
------------+------------------------+---------------------
 SH_CLERK   |                     20 |            64300.00
 AD_VP      |                      2 |            34000.00
 SA_MAN     |                      5 |            61000.00
 PU_MAN     |                      1 |            11000.00
 IT_PROG    |                      5 |            28800.00
 ST_CLERK   |                     20 |            55700.00
 MK_REP     |                      1 |             6000.00
 AD_PRES    |                      1 |            24000.00
 FI_ACCOUNT |                      5 |            39600.00
 AC_ACCOUNT |                      1 |             8300.00
(19 rows)

Using HAVING clause

If a table has been grouped using GROUP BY, but only certain groups are of interest, the HAVING clause can be used, much like a WHERE clause, to eliminate groups from the result.

The syntax is:

SELECT select_list 
FROM ... 
[WHERE ...] 
GROUP BY ... 
HAVING boolean_expression

Expressions in the HAVING clause can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function). Berikut adalah contoh penggunaanya di SQL:

Jika dijalankan hasilnya seperti berikut:

hr=# SELECT  job_id,
hr-#         count(*) count_employees_by_job,
hr-#         sum(salary) salary_group_by_job
hr-# FROM employees
hr-# GROUP BY job_id
hr-# HAVING count(*) >= 5;
   job_id   | count_employees_by_job | salary_group_by_job
------------+------------------------+---------------------
 SH_CLERK   |                     20 |            64300.00
 SA_MAN     |                      5 |            61000.00
 IT_PROG    |                      5 |            28800.00
 ST_CLERK   |                     20 |            55700.00
 PU_CLERK   |                      5 |            13900.00
 ST_MAN     |                      5 |            36400.00
 SA_REP     |                     30 |           250500.00
 FI_ACCOUNT |                      5 |            39600.00
(8 rows)

Different between WHERE and HAVING clause?

Mungkin dari temen-temen ada yang bertanya? jika menggunakan HAVING clause apa bedanya dengan WHERE clause?

Untuk mengetahui jawabanya kita kita perhatikan ilustrasi berikut:

ilustration-where-having-clause

Jadi klausa dengan WHERE dia prosesnya akan melakukan filter terlebih dahulu sebelum dilakukan proses GROUP BY sedangkan untuk HAVING dia akan memfilter datanya setelah dikelompokan / grouping. Berikut adalah contoh penggunaanya di SQL:

Jika dijalankan hasilnya seperti berikut:

hr=# SELECT  job_id,
hr-#         count(*) count_employees_by_job,
hr-#         sum(salary) salary_group_by_job
hr-# FROM employees
hr-# WHERE job_id in ('FI_ACCOUNT', 'SA_MAN', 'IT_PROG', 'HR_REP', 'MK_MAN')
hr-# GROUP BY job_id
hr-# HAVING sum(salary) >= 20000;
   job_id   | count_employees_by_job | salary_group_by_job
------------+------------------------+---------------------
 SA_MAN     |                      5 |            61000.00
 IT_PROG    |                      5 |            28800.00
 FI_ACCOUNT |                      5 |            39600.00
(3 rows)

Using GROUPING SET clause

More complex grouping operations than those described above are possible using the concept of grouping sets.

The data selected by the FROM and WHERE clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple GROUP BY clauses, and then the results returned. Berikut adalah contoh penggunaanya di SQL:

Jika dijalankan hasilnya seperti berikut:

hr=# SELECT manager_id, department_id, count(*), sum(salary)
hr-# FROM employees
hr-# GROUP BY GROUPING SETS ((manager_id), (department_id));
 manager_id | department_id | count |    sum
------------+---------------+-------+-----------
        103 |               |     4 |  19800.00
            |               |     1 |  24000.00
        101 |               |     5 |  44900.00
        122 |               |     8 |  23600.00
        121 |               |     8 |  25400.00
        114 |               |     5 |  13900.00
        102 |               |     1 |   9000.00
        205 |               |     1 |   8300.00
        146 |               |     6 |  51000.00
        108 |               |     5 |  39600.00
        147 |               |     6 |  46600.00
        201 |               |     1 |   6000.00
        120 |               |     8 |  22100.00
        100 |               |    14 | 155400.00
        124 |               |     8 |  23000.00
        145 |               |     6 |  51000.00
        123 |               |     8 |  25900.00
        148 |               |     6 |  51900.00
        149 |               |     6 |  50000.00
            |            70 |     1 |  10000.00
            |            80 |    34 | 304500.00
            |            20 |     2 |  19000.00
            |            10 |     1 |   4400.00
            |               |     1 |   7000.00
            |            90 |     3 |  58000.00
            |           100 |     6 |  51600.00
            |           110 |     2 |  20300.00
            |            30 |     6 |  24900.00
            |            50 |    45 | 156400.00
            |            40 |     1 |   6500.00
            |            60 |     5 |  28800.00
(31 rows)

Jika kita perhatikan query tersebut sama jika kita menjalankan 2 query tetapi hasilnya digabungkan menjadi 1 result sets, seperti berikut:

select manager_id, count(*), sum(salary)
from employees
group by manager_id;

select department_id, count(*), sum(salary)
from employees
group by department_id;