MySQL Group By

Explain the use of Group By with an example.

Group By is used along with aggregate functions to group the result-set by one or more columns.

Example : The query below will display employees along with their salaries.
SELECT employee_name,SUM(salary) FROM employee GROUP BY employee_name

What are important rules about its use?

1. An expression that occurs in the GROUP BY clause can appear in the SELECT clause.
2. An expression that is used to form groups can also occur in the SELECT clause within a compound expression.
3. If an expression occurs twice or more in a GROUP BY clause, double expressions are simply removed. E.g. The GROUP BY clause GROUP BY name, name is converted to GROUP BY name.
4. If a select statement does not have a GROUP BY clause, the column specified in the select clause must be used ion he aggregated function.

Difference between Having and Where.

The SQL where clause is tested against each and every row while the having clause is tested against groups and/or aggregates specified in the SQL GROUP BY clause. If The SQL statement contains both WHERE and HAVING clause , the where clause is applied first followed by HAVING

Order By vs Group By

Group by can be used along with some aggregate functions to sub-total the results of a query. On the other hand, Order by simply sorts the data. It has got nothing to do with the result set. Order by is associate with a column where as Group by associates all records together based on a column.

What is the Use of "WITH ROLLUP" in Mysql?

ROLL UP is a modifier used in GROUP BY that adds an extra row to the output. In a single query multi level analysis can be done. When ROLLUP is used, ORDER BY clause cannot be to sort the results.

Example:

A query below displays sum of salaries of employees year wise (group).
SELECT year, SUM(salary_amt) FROM salary GROUP BY year;

Output:

Year           Salary
2001           120000
2002           134000

If the total profit for these displayed years need to be shown, ROLLUP can be used.

Example:
SELECT year, SUM(salary_amt) FROM salary GROUP BY year WITH ROLLUP;

Output:

Year           Salary
2001           120000
2002           134000
NULL           254000

State and explain generic SQL syntax of SELECT command along with GROUP BY clause to sort data from MySQL table.

Group By is used along with aggregate functions to group the result-set by one or more columns.

Here, the employees are sorted by employee name.

Example : The query below will display employees along with their salaries.
SELECT employee_name,SUM(salary) FROM employee
GROUP BY employee_name
MySQL Table
MySQL Table - What are the different tables present in MySQL?, How can we repair a MySQL table?, What are HEAP tables in MySQL?, Explain the capabilities of command ALTER table...
MySQL Data Type
MySQL Data Type - Difference between CHAR and VARCHAR column types, Difference between BLOB and text column types, ENUM and SET column type....
MySQL Sequences
MySQL Sequences - How is Sequences handled in MySQL?, Explain the purpose of Auto_increment in MySQL, Explain to generate sequences without using Auto_increment, Explain the issues working with Auto_increment in MySQL...
Post your comment