Scribd
Upload
8 views12 pages

SQL Basic 3

The document provides an overview of SQL group functions, which operate on sets of rows to return a single result per group, including functions like AVG, COUNT, MAX, MIN, SUM, and others. It explains the use of the GROUP BY clause to aggregate data and the HAVING clause to filter grouped results, as well as how to handle null values in group functions. Additionally, it highlights common illegal queries and their solutions regarding the proper use of GROUP BY and HAVING clauses.

Uploaded by

bahatichacha698
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
8 views12 pages

SQL Basic 3

The document provides an overview of SQL group functions, which operate on sets of rows to return a single result per group, including functions like AVG, COUNT, MAX, MIN, SUM, and others. It explains the use of the GROUP BY clause to aggregate data and the HAVING clause to filter grouped results, as well as how to handle null values in group functions. Additionally, it highlights common illegal queries and their solutions regarding the proper use of GROUP BY and HAVING clauses.

Uploaded by

bahatichacha698
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

SQL basic

Group Function
• Group functions operate on sets of rows to
give one result per group
• AVG , COUNT, MAX, MIN, STDDEV, SUM,
VARIANCE
• They analyse group of data and return single
value
AVG,SUM,MIN, MAX
• They are used for numerical data

Select AVG(boom), MAX(boom),MIN(boom), SUM(boom) from student;

Select min(bdate),max(bdate) from student;

SELECT COUNT(*) FROM STUDENT;

SELECT COUNT(STUDENTID),COURSEID FROM STUDENT;

SELECT COUNT (DISTINCT COURSEID) FROM STUDENT;


Group Functions and Null Values
• Group functions ignore null values in the column
• NLV function forces group function to include null
values;
1 Select AVG(boom) from student;
2 Select AVG(NLV(boom,0)) from student;

NOTE: if the boom column is having null value to


some records the results of two queries will be
different. Number 1 total/those with values.
Number 2 total/all records.
Group by
SELECT column,GROUP_FUNCTION(column)
FROM table
[where condition]
[GROUP BY group_by_expression]
[ORDER BY column];

SELECT COURSEID,AVG(BOOM)
FROM STUDENT
GROUP BY COURSEID;
When using the GROUP BY clause, make sure that all
columns in select list that are not group functions are
included in the group by clause.
SELECT dept_id,job_id,sum(salary)
From employees
Group by dept_id,job_id;
ILEGAL Queries
SELECT DEPT_ID, COUNT(LAST_NAME)
FROM EMPLOYEES;

Solution
SELECT DEPT_ID,COUNT(LAST_NAME) FROM
EMPLOYEES
GROUP BY DEPT_ID;
ILLEGAL QUERIES
SELECT DEPT_ID, AVG(SALARY)
FROM EMPLOYEES
WHERE AVG(SALARY)>8000
GROUP BY DEPT_ID;

SOLUTION
SELECT DEPT_ID, AVG(SALARY)
FROM EMPLOYEES
HAVING AVG(SALARY)>8000
GROUP BY DEPT_ID;
Where clause is not used to restrict groups. HAVING
instead. Use it in the same way you use where.
Restricting Group results with having
SELECT COLUMN, GROUP_function
FROM table
[where condition]
[Group by group_by_expression]
[Having group_condition]
[order by column]

SELECT course,max(boom)
From student
Group by dept_id
Having max(boom)>200000;
What happens
1. Rows are grouped
2. The group function applied
3. Groups matching the having clause are
displayed
SELECT courseid,sum(boom)
From student
Where sex=“M”
Group by courseid
Having sum(boom)>1000000
Order by courseid;
NESTING GROUP FUNCTION
• SELECT MAX(AVG(BOOM))
• FROM STUDENT
• GROUP BY COURSEID;

You might also like