1GROUP BY: is used to collaborate
2with the SELECT statement to arrange
3matching data into groups.
4
5ORDER BY: is for sorting result
6either in descending or ascending order.
1 SELECT column_name(s)
2 FROM table_name
3 WHERE condition
4 GROUP BY column_name(s)
5 HAVING condition
6 ORDER BY column_name(s);
1/*Group by clause is used to group a selected set of rows into a set of summary
2rows by the values of one or more column or expression. It is always used in
3Conjunction with one or more aggregate function.*/
4SELECT AGGREGATE_FUNCTION(Column_Name) FROM Table_Name
5/*Group by exmaple*/
6SELECT city, sum(Salary) as TotalSalary FROM tblEmployee GROUP BY City
1-- It accepts a single column as a parameter and returns "1"
2-- if the column contains a null value generated as part of a
3-- subtotal by a ROLLUP or CUBE operation or "0" for any other value,
4-- including stored null values
5
6SELECT fact_1_id,
7 fact_2_id,
8 SUM(sales_value) AS sales_value,
9 GROUPING(fact_1_id) AS f1g,
10 GROUPING(fact_2_id) AS f2g
11FROM dimension_tab
12GROUP BY CUBE (fact_1_id, fact_2_id)
13HAVING GROUPING(fact_1_id) = 1 OR GROUPING(fact_2_id) = 1
14ORDER BY GROUPING(fact_1_id), GROUPING(fact_2_id);
1Multiple Row Functions (Group functions, Aggregate functions):
2(Count, MIN , MAX, AVG, SUM)
3will run for multiple rows and return a single value