SQL

🧠 Day 4: SQL Aggregate, Alias, and Date Functions Explained

Photo of Mathimaa Mathimaa3 weeks ago
0 0 1 minute read

🧠 Day 4: SQL Aggregate, Alias, and Date Functions Explained

📊 Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single summarized result.

  1. SUM() – Returns the total of all numeric values.

  2. AVG() – Returns the average value.

  3. MIN() – Returns the smallest value.

  4. MAX() – Returns the largest value.

  5. COUNT() – Counts all non-null values (includes duplicates).

  6. COUNT(DISTINCT) – Counts only unique non-null values (excludes duplicates).

🧩 Example:

SELECT SUM(salary) AS Total_Salary FROM employees;
SELECT AVG(salary) AS Average_Salary FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) AS TotalSalaryCount FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees;

📝 Note:

  • Aggregate functions ignore NULL values.

  • When using aggregate functions with other columns, GROUP BY is mandatory.

🏷️ Alias (Renaming Columns)

Alias is used to give a temporary name to columns or tables for readability.

SELECT UPPER(first_name) AS NEW_NAME FROM employees;
SELECT UPPER(first_name) NEW1_NAME FROM employees;

➡️ Both AS and space work for aliases.

⚖️ Difference Between WHERE and HAVING

Feature WHERE HAVING
Filters Rows before grouping Groups after aggregation
Works on Non-aggregated columns Aggregated values
Position Before GROUP BY After GROUP BY

🧩 Example:

SELECT department_id, SUM(salary)
FROM employees
WHERE department_id IN (100, 30, 90, 70)
GROUP BY department_id
HAVING department_id = 90;

📅 Date Functions in SQL

  1. SYSDATE → Returns the current date.

    SELECT SYSDATE FROM dual;

  2. SYSTIMESTAMP → Current date + time.

    SELECT SYSTIMESTAMP FROM dual;

  3. ADD_MONTHS(date, n) → Adds or subtracts months.

    SELECT ADD_MONTHS(SYSDATE, 2) FROM dual;
    SELECT ADD_MONTHS(SYSDATE, -2) FROM dual;

  4. MONTHS_BETWEEN(date1, date2) → Finds difference in months.

    SELECT MONTHS_BETWEEN('01-FEB-24','01-JAN-24') FROM dual;

  5. NEXT_DAY(date, day) → Returns next occurrence of the given weekday.

    • (1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri, 7=Sat)

    SELECT NEXT_DAY(SYSDATE, 1) FROM dual; -- Next Sunday
    SELECT NEXT_DAY(SYSDATE, 4) FROM dual; -- Next Wednesday

  6. LAST_DAY(date) → Returns last day of the month.

    SELECT LAST_DAY(SYSDATE) FROM dual;
    SELECT LAST_DAY('02-APR-24') FROM dual;

✅ Summary:

  • Aggregate functions help summarize data.

  • Aliases make results more readable.

  • WHERE filters data before grouping, HAVING after grouping.

  • Date functions simplify calculations with dates.

Photo of Mathimaa Mathimaa3 weeks ago
0 0 1 minute read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button