JDBC formula reference
Overview
This document provides a comprehensive reference for JDBC formulas supported in DataGOL. These formulas enable powerful data processing, analysis, and transformation capabilities directly within your DataGOL workflows.
What you can achieve?
-
Data aggregation: Calculate sums, averages, counts, and statistical measures
-
Text processing: Clean, format, and manipulate string data
-
Date/Time operations: Parse, format, and calculate with dates and timestamps
-
Conditional logic: Implement business rules and data validation
-
Mathematical operations: Perform calculations and statistical analysis
Mathematical functions
ABS - Absolute Value
Purpose:
Removes negative signs from numbers.
Usage:
ABS(number)
Example:
returns 25
ABS(-25)
Use Case:
Calculating distances, finding differences without caring about direction, cleaning up negative values in reports.
Mathematical functions
CEIL - Round Up
Purpose:
Always rounds numbers up to the next whole number.
Usage:
CEIL(number)
Example:
Returns 5
CEIL(4.1)
Use Case:
Used for calculating minimum packages needed, determining required resources, and inventory planning.
FLOOR - Round Down
Purpose:
Always rounds numbers down to the previous whole number.
Usage:
FLOOR(number)
Example:
Returns 4
FLOOR(4.9)
Use Case:
Useful for calculating completed units, determining available capacity, and budget calculations.
ROUND - Smart Rounding
Purpose:
Rounds numbers to the nearest whole number or specified decimal places.
Usage:
ROUND(number, decimal_places)
Example:
Returns 123.46
ROUND(123.456, 2)
Use Case:
Applied in financial calculations, displaying prices, and creating clean reports.
POWER - Exponents
Purpose:
Multiplies a number by itself a specified number of times.
Usage:
POWER(base, exponent)
Example:
Returns 8 (2×2×2)
POWER(2, 3)
Use Case:
Used for compound interest calculations, growth projections, and area/volume calculations.
SQRT - Square Root
Purpose:
Finds what number multiplied by itself equals the given number.
Usage:
SQRT(number)
Example:
Returns 4
SQRT(16)
Use Case:
Applied in statistical calculations, geometric measurements, and risk analysis.
LN - Natural Logarithm
Purpose:
Mathematical function for advanced calculations and modeling.
Usage:
LN(number)
Example:
Returns 2.302585
LN(10)
Use Case:
Used in scientific calculations, decay models, and financial modeling.
PI - Pi Constant
Purpose:
Returns the mathematical constant $\pi$ (3.14159...).
Usage:
PI()
Example:
Returns 3.14159
PI()
Use Case:
Used in circle calculations, geometric formulas, and engineering calculations.
MOD - Remainder
Purpose:
Shows what's left over after division.
Usage:
MOD(dividend, divisor)
Example:
Returns 1 (10÷3 = 3 remainder 1)
MOD(10, 3)
Use Case:
Useful for creating alternating patterns, pagination, and scheduling rotations.
Statistical functions
AVG - Average
Purpose:
Calculates the mean value of a group of numbers.
Usage:
AVG(column_name)
Example:
Might return 50000
AVG(salary)
Use Case:
Used for performance metrics, salary analysis, quality scores, and customer ratings.
SUM - Total
Purpose:
Adds up all numbers in a group.
Usage:
SUM(column_name)
Example:
Might return 125000
SUM(sales)
Use Case:
Used for revenue totals, expense calculations, inventory counts, and performance totals.
COUNT - Count Items
Purpose:
Counts how many items exist (ignores empty values).
Usage:
COUNT(column_name)
Example:
Might return 150
COUNT(customer_id)
Use Case:
Used for customer counts, order quantities, active users, and completion rates.
MIN - Smallest Value
Purpose:
Finds the lowest number in a group.
Usage:
MIN(column_name)
Example:
Might return 9.99
MIN(price)
Use Case:
Used for lowest prices, minimum scores, earliest dates, and budget floors.
MAX - Largest Value
Purpose:
Finds the highest number in a group.
Usage:
MAX(column_name)
Example:
Might return 98
MAX(score)
Use Case:
Used for peak performance, highest prices, latest dates, and record highs.
MEDIAN - Middle Value
Purpose:
Finds the middle number when all values are arranged in order.
Usage:
MEDIAN(column_name)
Example:
Might return 45000
MEDIAN(income)
Use Case:
Used for salary benchmarks, performance baselines, and market analysis.
STDDEV - Standard Deviation
Purpose:
Measures how spread out numbers are from the average.
Usage:
STDDEV(column_name)
Example:
Might return 2.5
STDDEV(response_time)
Use Case:
Used for quality control, performance consistency, and risk assessment.
VARIANCE - Variance
Purpose:
Measures how much numbers vary from the average (squared differences).
Usage:
VARIANCE(column_name)
Example:
Might return 1250000
VARIANCE(sales)
Use Case:
Used for financial analysis, quality metrics, and forecasting accuracy.
Text functions
CONCAT - Join Text
Purpose:
Combines multiple pieces of text into one.
Usage:
CONCAT(text1, text2, text3...)
Example:
Returns "Hello World"
CONCAT('Hello', ' ', 'World')
Use Case:
Used for creating full names, building addresses, generating reports, and email formatting.
UPPER - Make Uppercase
Purpose:
Converts all letters to capital letters.
Usage:
UPPER(text)
Example:
Returns "HELLO"
UPPER('hello')
Use Case:
Used for standardizing data entry, creating consistent formats, and report headers.
LOWER - Make Lowercase
Purpose:
Converts all letters to small letters.
Usage:
LOWER(text)
Example:
Returns "hello"
LOWER('HELLO')
Use Case:
Used for email addresses, usernames, data cleaning, and search functions.
INITCAP - Capitalize Words
Purpose:
Makes the first letter of each word uppercase.
Usage:
INITCAP(text)
Example:
Returns "John Smith"
INITCAP('john smith')
Use Case:
Used for proper names, titles, address formatting, and professional correspondence.
TRIM - Remove Extra Spaces
Purpose:
Removes spaces from the beginning and end of text.
Usage:
TRIM(text)
Example:
Returns "Hello World"
TRIM(' Hello World ')
Use Case:
Used for data cleaning, form processing, and import/export cleanup.
LTRIM - Remove Left Spaces
Purpose:
Removes spaces only from the beginning of the text.
Usage:
LTRIM(text)
Example:
Returns "Hello"
LTRIM(' Hello')
Use Case:
Used for data formatting, code cleanup, and text alignment.
SUBSTRING - Extract Text Portion
Purpose:
Takes out a specific part of the text.
Usage:
SUBSTRING(text, start_position, length)
Example:
Returns "atab"
SUBSTRING('Database', 2, 4)
Use Case:
Used for extracting codes, parsing data, creating abbreviations, and ID processing.
RIGHT - Get End Characters
Purpose:
Takes a specified number of characters from the end of the text.
Usage:
RIGHT(text, number_of_characters)
Example:
Returns "base"
RIGHT('Database', 4)
Use Case:
Used for file extensions, postal codes, ID suffixes, and validation codes.
REGEXP_REPLACE - Pattern Replacement
Purpose:
Finds and replaces text patterns using advanced matching rules.
Usage:
REGEXP_REPLACE(text, pattern, replacement, flags)
Example:
Returns 1234567890
REGEXP_REPLACE('123-456-7890', '-', '', 'g')
Use Case:
Used for phone number formatting, data cleaning, removing special characters, and standardizing formats.
Date functions
CURRENT_DATE - Today's Date
Purpose:
Returns today's date.
Usage:
CURRENT_DATE
Example:
Returns 2025-01-22
CURRENT_DATE
Use Case:
Used for timestamping records, calculating ages, due date calculations, and report dating.
CURRENT_TIMESTAMP - Current Date and Time
Purpose:
Returns the exact current date and time.
Usage:
CURRENT_TIMESTAMP
Example:
Returns 2025-01-22 10:15:30
CURRENT_TIMESTAMP
Use Case:
Used for transaction logging, audit trails, real-time reporting, and activity tracking.
AGE - Calculate Time Difference
Purpose:
Calculates the number of days between two dates.
Usage:
AGE(end_date, start_date)
Example:
Returns 7
AGE('2025-01-22', '2025-01-15')
Use Case:
Used for customer tenure, project duration, payment delays, and service periods.
DATE_PART - Extract Date Component
Purpose:
Pulls out specific parts of a date (year, month, day, etc.).
Usage:
DATE_PART('part', date)
Example:
Returns 2025
DATE_PART('year', '2025-01-22')
Use Case:
Used for monthly reports, yearly summaries, seasonal analysis, and age calculations.
DATE_TRUNC - Round Date
Purpose:
Rounds dates down to a specific time period.
Usage:
DATE_TRUNC('precision', date)
Example:
Returns 2025-01-01
DATE_TRUNC('month', '2025-01-22')
Use Case:
Used for monthly grouping, quarterly reports, trend analysis, and data aggregation.
Utility functions
COALESCE - Handle Missing Data
Purpose:
Returns the first non-empty value from a list.
Usage:
COALESCE(value1, value2, value3...)
Example:
Returns "Fallback"
COALESCE(NULL, 'Fallback', 'Default')
Use Case:
Used for default values, handling missing data, report formatting, and data validation.
CASE WHEN - Conditional Logic
Purpose:
Creates an if-then-else logic for different scenarios.
Usage:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END
Example:
Assigns 'A' if score >= 90, 'B' if score >= 80, else 'C'
CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END
Use Case:
Used for grade calculations, status assignments, categorization, conditional formatting, and business rules.
Best practices
Naming conventions
Column and table names
-- ✅ GOOD: Clear, descriptive names
SELECT customer_first_name, order_total_amount
FROM customer_orders
-- ❌ AVOID: Cryptic abbreviations
SELECT cust_fn, ord_tot_amt
FROM cust_ord
Formula aliases
-- ✅ GOOD: Meaningful aliases
SELECT
CONCAT(first_name, ' ', last_name) AS full_name,
AVG(salary) AS average_salary,
COUNT(*) AS total_employees
-- ❌ AVOID: Generic or no aliases
SELECT
CONCAT(first_name, ' ', last_name),
AVG(salary) AS avg1,
COUNT(*) AS cnt
Code formatting
Readable structure
-- ✅ GOOD: Well-formatted and indented
SELECT
department_name,
COUNT(employee_id) AS employee_count,
AVG(salary) AS average_salary,
MAX(hire_date) AS latest_hire_date
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE hire_date >= '2020-01-01'
GROUP BY department_name
ORDER BY average_salary DESC;
-- ❌ AVOID: Everything on one line
SELECT department_name,COUNT(employee_id),AVG(salary),MAX(hire_date) FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE hire_date >= '2020-01-01' GROUP BY department_name ORDER BY AVG(salary) DESC;
Function spacing
-- ✅ GOOD: Consistent spacing
CASE
WHEN score >= 90 THEN 'Excellent'
WHEN score >= 80 THEN 'Good'
WHEN score >= 70 THEN 'Average'
ELSE 'Needs Improvement'
END AS performance_rating
-- ❌ AVOID: Inconsistent spacing
CASE WHEN score>=90 THEN'Excellent'WHEN score>=80 THEN 'Good'ELSE'Poor'END
Performance optimization
Use appropriate functions
-- ✅ GOOD: Efficient for large datasets
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(total_amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- ❌ AVOID: Less efficient
SELECT
SUBSTRING(order_date::text, 1, 7) AS order_month,
SUM(total_amount) AS monthly_revenue
FROM orders
GROUP BY SUBSTRING(order_date::text, 1, 7);
Minimize function calls in WHERE clauses
-- ✅ GOOD: Index-friendly
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';
-- ❌ AVOID: Prevents index usage
SELECT * FROM orders
WHERE DATE_PART('year', order_date) = 2024;
Null handling
Always plan for null values
-- ✅ GOOD: Explicit NULL handling
SELECT
customer_name,
COALESCE(phone_number, 'No phone provided') AS contact_phone,
COALESCE(email, 'No email provided') AS contact_email
FROM customers;
-- ✅ GOOD: Safe division
SELECT
product_name,
CASE
WHEN total_orders > 0
THEN ROUND(total_revenue / total_orders, 2)
ELSE 0
END AS average_order_value
FROM product_summary;
COUNT function considerations
-- ✅ GOOD: Understand the difference
SELECT
COUNT(*) AS total_rows, -- Counts all rows
COUNT(email) AS customers_with_email, -- Counts non-NULL emails
COUNT(DISTINCT city) AS unique_cities -- Counts unique non-NULL cities
FROM customers;
Data type awareness
Consistent data types
-- ✅ GOOD: Proper type handling
SELECT
order_id,
ROUND(total_amount, 2) AS formatted_total, -- Keep as numeric
TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date -- Convert to text when needed
FROM orders;
-- ❌ AVOID: Unnecessary type conversions
SELECT
order_id::text, -- Unnecessary conversion
total_amount::text -- Loses numeric properties
FROM orders;
Date handling
-- ✅ GOOD: Timezone aware
SELECT
order_date,
DATE_TRUNC('day', order_date AT TIME ZONE 'UTC') AS order_day_utc
FROM orders;
-- ✅ GOOD: Clear date arithmetic
SELECT
customer_id,
registration_date,
AGE(CURRENT_DATE, registration_date) AS customer_tenure_days
FROM customers;
Conditional logic best practices
Clear CASE statements
-- ✅ GOOD: Comprehensive and readable
SELECT
employee_id,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 75000 THEN 'Mid-Level'
WHEN salary >= 50000 THEN 'Junior'
WHEN salary >= 30000 THEN 'Entry-Level'
ELSE 'Intern'
END AS salary_band,
CASE
WHEN department_id IS NULL THEN 'Unassigned'
ELSE department_name
END AS department_display
FROM employees;
Nested functions
-- ✅ GOOD: Logical nesting with comments
SELECT
customer_id,
-- Clean and format customer names
INITCAP(
TRIM(
COALESCE(customer_name, 'Unknown Customer')
)
) AS formatted_customer_name
FROM customers;
Aggregation best practices
Meaningful grouping
-- ✅ GOOD: Clear grouping logic
SELECT
DATE_TRUNC('quarter', order_date) AS quarter,
product_category,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(order_id) AS total_orders,
SUM(order_total) AS total_revenue,
AVG(order_total) AS average_order_value,
STDDEV(order_total) AS revenue_volatility
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE order_date >= '2024-01-01'
GROUP BY
DATE_TRUNC('quarter', order_date),
product_category
HAVING COUNT(order_id) >= 10 -- Only include categories with significant volume
ORDER BY quarter DESC, total_revenue DESC;
Documentation & comments
Self-documenting queries
-- ✅ GOOD: Clear purpose and logic
-- Customer Lifetime Value Analysis
-- Calculates CLV for customers active in the last 2 years
SELECT
c.customer_id,
c.customer_name,
-- Customer tenure in days
AGE(CURRENT_DATE, c.registration_date) AS customer_age_days,
-- Purchase behavior metrics
COUNT(o.order_id) AS total_orders,
SUM(o.order_total) AS lifetime_revenue,
AVG(o.order_total) AS average_order_value,
-- Recency calculation
AGE(CURRENT_DATE, MAX(o.order_date)) AS days_since last order,
-- Customer lifetime value estimate
CASE
WHEN COUNT(o.order_id) > 0 THEN
ROUND(
SUM(o.order_total) /
NULLIF(AGE(CURRENT_DATE, c.registration_date), 0) * 365,
2
)
ELSE 0
END AS estimated_annual_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.registration_date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY c.customer_id, c.customer_name, c.registration_date
HAVING COUNT(o.order_id) > 0 -- Only customers with purchases
ORDER BY estimated_annual_value DESC;
Error prevention
Defensive programming
-- ✅ GOOD: Prevent division by zero and handle edge cases
SELECT
product_id,
product_name,
total_inventory,
total_sold,
-- Safe percentage calculation
CASE
WHEN total_inventory > 0 THEN
ROUND((total_sold::decimal / total_inventory) * 100, 2)
ELSE 0
END AS sell_through_percentage,
-- Safe average calculation
CASE
WHEN order_count > 0 THEN
ROUND(total_revenue / order_count, 2)
ELSE 0
END AS average order value
FROM product_metrics;
Input validation
-- ✅ GOOD: Validate and clean inputs
SELECT
customer_id,
-- Clean phone numbers
REGEXP_REPLACE(
REGEXP_REPLACE(phone, '[^0-9]', '', 'g'), -- Remove non-digits
'^1?(.{10})$',
'\1'
) AS cleaned_phone,
-- Validate email format
CASE
WHEN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
THEN LOWER(TRIM(email))
ELSE NULL
END AS validated_email
FROM customers;
Testing & validation
Data quality checks
-- ✅ GOOD: Include data validation in your queries
SELECT
'Customer Data Quality' AS report_type,
COUNT(*) AS total_customers,
COUNT(CASE WHEN email IS NOT NULL THEN 1 END) AS customers_with_email,
COUNT(CASE WHEN phone IS NOT NULL THEN 1 END) AS customers_with_phone,
COUNT(CASE WHEN registration_date > CURRENT_DATE THEN 1 END) AS future registration dates,
AVG(CASE WHEN customer_name IS NOT NULL THEN LENGTH(customer_name) END) AS avg_name_length
FROM customers;
Common anti-patterns to avoid
What not to do
-- ❌ AVOID: Using functions unnecessarily
WHERE UPPER(status) = 'ACTIVE' -- Instead create index on status and use = 'ACTIVE'
-- ❌ AVOID: Complex nested subqueries when JOINs work better
SELECT * FROM customers WHERE customer_id IN (
SELECT customer_id FROM orders WHERE order_date IN (
SELECT MAX(order_date) FROM orders GROUP BY customer_id
)
);
-- ❌ AVOID: Not handling NULLs in aggregations
AVG(rating) -- Might not behave as expected with NULLs
-- ❌ AVOID: Hardcoded values without context
WHERE status_code = 3 -- Use meaningful names: WHERE status = 'ACTIVE'
Summary checklist
Before deploying your query, ensure to check the following:
-
Column names are clear and descriptive
-
Functions have meaningful aliases
-
Code is properly formatted and indented
-
NULL values are explicitly handled
-
Data types are appropriate and consistent
-
Performance implications are considered
-
Edge cases (division by zero, empty sets) are handled
-
Comments explain complex logic
-
Query has been tested with sample data
-
Results are validated for accuracy
Performance review
Review the following before deploying the query:
-
WHEREclauses don't prevent index usage -
Appropriate functions are used for data types
-
Aggregations are efficient
-
Complex calculations are broken into readable steps
-
Query execution plan has been reviewed