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
