Fundamental SQL Using FUNCTION and GROUP BY

SCALAR VS AGGREGATE FUNCTION

Functions are methods used to perform data operations on the database. These operations can be numerical calculations such as sum, count, avg, etc; or non-numeric operations such as string concatenations and sub-strings. SQL functions can be divided into 2 categories, namely scalar functions and aggregate functions.

Scalar Functions (Numeric Value)

This function is generally used when you want to perform mathematical operations in SQL quickly and effectively. In SQL itself there are many mathematical functions.

- Numerical Scalar Function — ABS()

Syntax:

- Numerical Scalar Function — CEILING()

Syntax:

- Numerical Scalar Function — FLOOR()

Syntax:

- Numerical Scalar Function — ROUND()

Syntax:

- Numerical Scalar Function — SQRT()

Syntax:

TEXT Functions

This function is used if we want to perform operations on text or characters in SQL, for example, changing lowercase to uppercase, counting the number of characters from text, etc.

- TEXT Functions — CONCAT()

Syntax:

- TEXT Functions — SUBSTRING_INDEX()

Syntax:

  • delimiter → characters or a combination of characters to break the text in the column,
  • index_to_return → the index of the fractional text to be retrieved.

- TEXT Functions — SUBSTR()

Syntax:

  • Start Index -> index of the text (starting from 1)
  • Number of string to be extract -> the number of characters to be extract.

- TEXT Functions — LENGTH()

Syntax:

- TEXT Functions — REPLACE()

Syntax:

  • Character/String to be change → the string / character to be replaced
  • New String/Character → new string / character in place of the previous string / character

Aggregate Functions

This aggregate function is used to perform calculations on a group of values.

- Aggregate Functions— SUM()

Syntax:

- Aggregate Functions — COUNT()

Syntax:

- Aggregate Functions — AVERAGE()

Syntax:

GROUP BY

To group data in SQL we use GROUP BY Statement. The GROUP BY statement will group data of the same value into one group, and by using an aggregate function such as (COUNT, MAX, MIN, SUM, AVG) we can aggregate for each group or category that is formed.

  1. GROUP BY is placed after the WHERE, but if you don’t use WHERE, it is immediately placed after FROM.
  2. If using ORDER BY, GROUP BY is placed before ORDER BY.
  1. Group by Multiple Columns, data is grouped using criteria from two or more columns, for example grouping data by province and brand.
some output

CASE … WHEN …

  1. Total quantity of all products sold.
  2. Total quantity and total revenue for each product code.
  3. Average total spend of each kode_pelanggan
  4. Also, don’t forget to add a new column called ‘category’ which categorizes total / revenue into 3 categories: High:> 300K; Medium: 100K — 300K; Low: <100K.
  • Use GROUP BY to group data and understand the concept of combining GROUP BY with an aggregate function
  • Use of the CASE Statement for decision making structure.
  • Working on a mini project which is an integration of the whole material and of course the materials in the previous modules to solve business problems.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store