Header Ads

SQL :: WHERE...GROUP BY...HAVING...ORDER BY Clause techniques

GROUP BY:
The GROUP BY clause can be used in SELECT statement by grouping up the data from multiple records from a table (or tables), and at least one arithmetic operator should appear on SELECT statement.

Syntax:

SELECT Column1, Column2, Aggreate function
FROM TableName
WHERE predicates
GROUP BY Column1, Column2
HAVING Aggreate function Condition
ORDER BY Column ASC / DESC

Let see how Group By clause works. Since eveyone is familiar on Student related stuff, the schema is created on that. please note that the table is not normalized, just to demonstrate, all the fields have been put in a single table. Scripts have been attached to create the table, and with the values.

SELECT StudentName, SubjectName, Mark FROM dbo.StudentMark;

SELECT StudentName, SubjectName, Mark FROM dbo.StudentMark;

The above table contains the mark details for each student.

Problem: How much marks each student scored totally.

To achieve this, how do we manually do in paper?

* First, needs to arrange the name-wise.
* Sum up the Marks for each Student.

GROUP BY StudentName

GROUP BY example

SUM(Mark)

GROUP BY SUM example

Let’s write the SQL:

SELECT StudentName, SUM(Mark) AS 'Total for Each Student'
FROM dbo.StudentMark
GROUP BY StudentName;

Result:

GROUP BY SUM RESULT

In above SQL Statement, there is an aggregate function SUM is used to sum it up all the marks for each student.
Okay. Where it is specified as each student? Yeah, that is mentioned in Group by Clause.

Well. If I ask you what will I get the result if it groups up with Student Name, and Subject? You will get the total of marks which each student scored for each subject. Since there is no multiple occurrences for each student and subject in the table which gives the result same as mentioned in Picture 1.

Below are the examples given how to use the aggregate function in SELECT clause.

Example using COUNT function:

SELECT StudentName, COUNT(*) AS 'Number of Subjects'
FROM dbo.StudentMark
GROUP BY StudentName;

GROUP BY COUNT example

Example using AVG function:

SELECT StudentName, AVG(Mark) AS 'Average for each Student'
FROM dbo.StudentMark
GROUP BY StudentName;

GROUP BY AVG example

Example using MIN function:

SELECT StudentName, MIN(Mark) AS 'Lowest Mark Scored by each Student'
FROM dbo.StudentMark
GROUP BY StudentName;

GROUP BY MIN example

Example using MAX function:

SELECT StudentName, MAX(Mark) AS 'Highest mark scored by each Student'
FROM dbo.StudentMark
GROUP BY StudentName

GROUP BY MAX example
HAVING:

* HAVING is generally used with the GROUP BY clause, and mainly used to manipulate and filter the data by using aggregate functions. When GROUP BY is not used, HAVING behaves like a WHERE clause.
* HAVING was added to SQL because the WHERE clause can not be used against aggregate functions (like SUM)
For example,

SELECT StudentName, SUM(Mark) AS 'Students Scored > 35'
FROM dbo.StudentMark
GROUP BY StudentName
HAVING SUM(Mark) > 35

HAVING WITH BETWEEN
* One important thing to notice that you can't select the columns other than mentioned in the GROUP BY clause. for example, You can't use the column 'Subject' in SELECT clause.


* Below example shows how to use the HAVING function with BETWEEN.

SELECT StudentName, SUM(Mark) AS 'Students Scored 20 - 45'
FROM dbo.StudentMark
GROUP BY StudentName
HAVING SUM(Mark) BETWEEN 20 AND 45

HAVING WITH SUM
* You could use different aggregate functions in SELECT Clause and HAVING function. In below example, the SUM and COUNT are used in SELECT clause, and HAVING respectively.

SELECT StudentName, SUM(Mark) AS 'Student who scored on 3 subjects'
FROM dbo.StudentMark
GROUP BY StudentName
HAVING COUNT(*) = 3

HAVING WITH SUM AND COUNT

ORDER BY:

* The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.
* The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, it is sorted by ASC.
* ASC indicates sort based on the ascending order which is default.
DESC indicates sort based on the descending order.
* One point to note, When you use ORDER BY Clause along with GROUP BY, the columns which are mentioned in the ORDER BY Clause should be one of the columns specified in GROUP BY clause.

The column StudentName is specified on GROUP BY Clause which works.

SELECT StudentName, SUM(Mark) AS 'Students Scored > 35'
FROM dbo.StudentMark
GROUP BY StudentName
HAVING SUM(Mark) > 35
ORDER BY StudentName DESC;
But, The below example will not work since the Column Mark is not specified in GROUP BY Clause.

SELECT StudentName, SUM(Mark) AS 'Students Scored > 35'
FROM dbo.StudentMark
GROUP BY StudentName
HAVING SUM(Mark) > 35
ORDER BY Mark DESC;
* ORDER BY clause can be used only on SELECT statements even without specifying GROUP BY, HAVING, WHERE clause. Basically, it is used to sort the result set once it is retrieved.

SELECT StudentName, SubjectName, Mark FROM dbo.StudentMark
ORDER BY Mark DESC;

ORDER BY
* More than one columns also can be specified with different order for each. In below example, first the result set will be sorted witih StudentName with ascending order, and then Mark will be ordered as descending order for within each StudentName.

SELECT StudentName, SubjectName, Mark FROM dbo.StudentMark
ORDER BYStudentName ASC, Mark DESC;

ORDER BY

WHERE:
Since we have seen major part of the SELECT statement, let me finish this article with WHERE Clause.

* WHERE clause is mainly used to filter out the values at first stage which gets executed once the table is identified.
* Let see the order of executing the Statements:
1. FROM - access the table
2. WHERE - filter the data which is taken
3. GROUP BY - grouping the data according to the columns specified
4. HAVING - filters the data once the data got grouped.
5. ORDER BY - sorts the values once the data got retrieved according to the predicates.
* For example,

SELECT StudentName, SUM(Mark) AS 'Students Scored > 35'
FROM dbo.StudentMark
WHERE Mark > 10
GROUP BY StudentName
HAVING SUM(Mark) > 35
ORDER BY StudentName DESC;
* When you look at the above example, the Column Mark is specified even though which is not specified in GROUP BY Clause. Can you tell me why..? :-)
Yeah. Since the WHERE clause gets executed before Group By clause, it could able to access any columns from the tables.

Hope this article helps you to understand How to use Group by Clause, HAVING, ORDER BY with WHERE. Try to play around this with different combinations. Cheers...

No comments:

Powered by Blogger.