[go: up one dir, main page]
Scribd
Upload
5 views7 pages

dbms lab 4

The document explains SQL aggregate functions, which summarize data across multiple rows, including COUNT(), SUM(), AVG(), MAX(), and MIN(). It also covers the GROUP BY and HAVING clauses for organizing and filtering grouped data, as well as date and scalar functions for manipulating data. Examples illustrate how to use these functions in SQL queries to generate reports and analytics.

Uploaded by

Prerana laha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views7 pages

dbms lab 4

The document explains SQL aggregate functions, which summarize data across multiple rows, including COUNT(), SUM(), AVG(), MAX(), and MIN(). It also covers the GROUP BY and HAVING clauses for organizing and filtering grouped data, as well as date and scalar functions for manipulating data. Examples illustrate how to use these functions in SQL queries to generate reports and analytics.

Uploaded by

Prerana laha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

Aggregate Functions in SQL :

Aggregate functions are used in SQL to perform calculations on a set of


values (multiple rows) and return a single summarized value. These
functions are typically used in reports and analytics to summarize large
amounts of data.

Example
Function Description
Usage

Returns the total number of rows or non-null


COUNT() COUNT(*)
values in a column.

Returns the total sum of values in a numeric


SUM() SUM(salary)
column.

Returns the average (mean) value of a numeric


AVG() AVG(price)
column.

MAX() Returns the highest value in a column. MAX(marks)

MIN() Returns the lowest value in a column. MIN(grade)

Example Query using Aggregate Functions:


SELECT

COUNT(*) AS TotalOrders,

SUM(order_amount) AS TotalSales,

AVG(order_amount) AS AverageOrder,

MAX(order_amount) AS HighestOrder,

MIN(order_amount) AS LowestOrder

FROM Orders;
This query summarizes all orders: total number, total sales amount, average
order value, highest and lowest order value.

GROUP BY Clause
The GROUP BY clause is used to arrange identical data into groups, so
that aggregate functions can be applied per group, rather than across the
whole table.
Example Query with GROUP BY:
SELECT customer_id, COUNT(*) AS OrderCount
FROM Orders
GROUP BY customer_id;

• Here, each customer becomes a group.


• COUNT(*) is calculated per customer, showing how many orders
each one has made.

HAVING Clause
The HAVING clause is used to filter the grouped data, based on
conditions involving aggregate functions. It's like a WHERE clause, but it
applies after grouping has taken place.
Example Query with GROUP BY and HAVING:
SELECT customer_id, COUNT(*) AS OrderCount
FROM Orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
• This query filters out those customers who have placed 5 or fewer
orders.
• Only customers with more than 5 orders are shown in the result.

Group By .............Having Clause


The Order of Clauses in the SELECT Statement
SELECT column(s)
FROM table(s)
WHERE row condition(s)
GROUP BY column(s)
HAVING group of rows condition(s)
ORDER BY column(s);

GROUP BY : The GROUP BY clause can be used to divide the rows in a


table into
smaller groups. Group functions may be used to return summary
information for each group.
Example: To calculate the average salary for each different job type.
SQL> SELECT JOB, AVG(SAL)
FROM EMPLOYEE
GROUP BY JOB;

HAVING: The HAVING clause is very similar to WHERE clause except the
statements within it is of an aggregate nature
Example: To display the average salary for all departments employing
more than three people.

SQL> SELECT DEPTNO, AVG(SAL)


FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3;

SQL> SELECT DEPTNO, JOB, SUM(SAL) AS salary


FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

SQL> SELECT DEPTNO, JOB, SUM(SAL) AS salary


FROM EMP
GROUP BY CUBE(DEPTNO, JOB);

Date Functions in SQL


Date functions help you manipulate and extract parts of date and
time data. Common ones include:
Function Description

YEAR(date) Extracts year from a date

MONTH(date) Extracts month from a date

DAY(date) Extracts day from a date

NOW() or CURRENT_DATE() Returns current system date

DATE() Extracts date part only (without time)


Example: Grouping by Year
SELECT YEAR(order_date) AS OrderYear, SUM(order_amount) AS TotalSales
FROM Orders
GROUP BY YEAR(order_date);

• This query shows the total sales for each year.

Example: Count Orders Per Month in Current Year


SELECT MONTH(order_date) AS OrderMonth, COUNT(*) AS TotalOrders
FROM Orders
WHERE YEAR(order_date) = YEAR(CURRENT_DATE())
GROUP BY MONTH(order_date);

• This filters orders of the current year only and shows monthly
order counts.
Scalar Functions in SQL
Scalar functions operate on individual values and return a single value per
row.

Function Description

UPPER(string) Converts text to uppercase

LOWER(string) Converts text to lowercase

LENGTH(string) Returns the length of a string

ROUND(number, Rounds a number to a given number of decimal


decimals) places

ABS(number) Returns absolute value

Example: Using ROUND with AVG


SELECT customer_id, ROUND(AVG(order_amount), 2) AS AverageOrder
FROM Orders
GROUP BY customer_id;

• Here, average order amount is rounded to 2 decimal places for


each customer.
Combined Example :
(Aggregate + Date + Group By + Having + Scalar)
SELECT
customer_id,
YEAR(order_date) AS OrderYear,
COUNT(*) AS TotalOrders,
ROUND(SUM(order_amount), 2) AS TotalSpent
FROM Orders
GROUP BY customer_id, YEAR(order_date)
HAVING SUM(order_amount) > 1000;

• Grouping: By customer_id and OrderYear.


• Aggregate functions: COUNT(), SUM().
• Scalar function: ROUND() used on SUM.
• Date function: YEAR(order_date).
• HAVING clause: Filters customers who spent more than 1000 in
a year.

You might also like