In this article we will provide comprehensive notes on MySQL Queries IP Class12 for the topics group by, having, and order by. Read the article and learn with fun!
As we have discussed following topics from MySQL part, open the links and know more if you missed them.
- Maths & Text Functions: Notes | Assignments
- Date Functions and Aggregate Functions: Notes
Topics Covered
Group By, having and Order by MySQL Queries IP Class12
Now you are familiar with basic MySQL commands. In this section of MySQL Queries IP Class12 we will discuss some more commands like group by, having and order by to retrieve data in more efficient way. So let us have a look on use of each of them.
- Group By – This clause is used to group the query results. It will display the results in a group of particular values from the columns specified with group by clause in MySQL query.
- Having – Having clause applies conditions on the group by clause. The conditions can be written as we write where clause in MySQL Commands.
- Order By – The Order by clause is used to sort the query results either in Ascending order or Descending order.
In the next section of MySQL Queries IP Class12 we will discuss the group by clause.
The Group By Clause – MySQL Queries IP Class12
As we discussed, the group by clause display the query results in specific group of values on the specified fields.
For example, If a table has a field named city. Now if you want to display the query results according to cities, you can you group by clause. It divide the table in the particular group of values.
As it is used with column name, in addition to this the group by clause can be also used with aggregate functions to display the value for each group.
For example, if you want to count the frequencies of customers from a particular city. You can use group by with count function.
Now observe this query:
select designation, count(*) from employees group by designation;
It shows the number of managers, engineers, and other designations available in the employees table.
Now observer this query and understand the output:
select department, count(*), sum(salary) from employees group by department;
Points to be remembered:
- The group by clause always written after from clause.
- The column which is to be grouped will be written in the select clause and group by clause.
After getting familiar with the group by clause let’s explore the next topic of MySQL Queries IP Class12 i.e. the having clause.
The having clause – MySQL Queries IP Class12
The having clause is used to apply a condition in the group by query results. It is working like where clause but there is one difference between them i.e. the WHERE clause can’t include aggregate functions whereas the HAVING clause can include them.
Now let’s we write the queries we discussed in the previous section with having clause.
select designation, count(*) from employees group by designation having designation in ('engineer','manager');
This query returns number of engineers and managers available in the employees table.
The having clause can be used in this manner also:
select department, sum(salary) from employees group by designation having sum(salary)>=5000;
The above query filters only those records which consist of the sum of salary more than 5000 in the department column of the employees table.
You can use all of the relational operators, membership operators, conditional operators etc.
You can also display non-group expression with group by in the following manner:
select empname, sum(salary) from employees group by department;
But when you are going to use such queries, they didn’t show the accurate the results.
In the next section of MySQL Queries IP Class12 we will discuss the order by clause.
The order by clause – MySQL Queries IP Class12
As you know when we are using SQL queries, the results are not available with proper order. It is displayed as it is inserted in the table by default.
To get the results in proper order, order by clause is useful. The order by clause will be the last part of the select query in MySQL probably. Observe the following query:
select * from employees order by empname;
This command display the result in ascending order. You can use order by clause with where condition as well.
select empname,salary from employees where salary>=2500 order by salary;
You can use any of the column with order by clause. You can use order by clause with multiple columns in following manner:
select empname, salary from employees where salary>=2500 order by salary, order by ename ;
For accurate results, use only one order by clause in the query. As it will sort the columns as specified order by clause in the query.
You can sort the records in descending order by using DESC key word. Observe the following code:
select empname, salary from employees order by salary desc;
To sort the records in ascending order ASC keyword is used. By default, the results are available in ascending order by itself.
You can also sort the results produced with expression in select query. As it is written with select clause.
select empname, salary * 12 from employees order by salary*12 asc;
Thank you for visiting out blog. Share this article with your friends and groups.
Feel free to ask you doubts, give your valuable feedback, your views about this article in the comment section.
Follow the below given link to download the contents in PDF.