group function in sql code example

Example 1: sql group by

# Say you have a table called SALARIES that contains a 
# few duplicate NAME entries...
+----+-------------+--------+
| ID |    NAME     | SALARY |
+----+-------------+--------+
| 1	 |     Bob     |  500   |
| 2  |    Alice    |  500   |
| 3  |    Alice    |  200   |
| 4  |    Frank    |  700   |
| 5  |    Percy    |  100   |
| 6  |    Percy    |  800   |
| 7  |   Cyrille   |  400   |
+----+-------------+--------+

# We can obtain the total salaries of each person
# by using GROUP BY in the following query...
SELECT NAME, SALARY FROM SALARIES GROUP BY NAME;

# Which will output the following...
+------------+--------+
|   Alice    |  700   |
|    Bob     |  500   |
|  Cyrille   |  400   |
|   Frank    |  700   |
|   Percy    |  900   |
+------------+--------+

Example 2: grouping sql

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'  
FROM Sales.SalesPerson  
GROUP BY SalesQuota WITH ROLLUP;  
GO

Example 3: sql group by example

SELECT column_name(s)
  FROM table_name
  WHERE condition
  GROUP BY column_name(s)
  HAVING condition
  ORDER BY column_name(s);

Example 4: group function in sql

--- GROUP FUNCTION | MULTI ROW FUNCTION | AGGREGATE FUNCTION 
--- COUNT , MAX , MIN , SUM , AVG

Example 5: GROUPING Functions

-- It accepts a single column as a parameter and returns "1" 
-- if the column contains a null value generated  as part of a
-- subtotal by a ROLLUP or CUBE operation or "0" for any other value,
-- including stored null values

SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING(fact_1_id) AS f1g, 
       GROUPING(fact_2_id) AS f2g
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
HAVING GROUPING(fact_1_id) = 1 OR GROUPING(fact_2_id) = 1
ORDER BY GROUPING(fact_1_id), GROUPING(fact_2_id);

Example 6: GROUP BY

SELECT <field1, field2, field3…>
FROM <table1_name>
WHERE <condition/expression>
GROUP BY <field1, field2, field3…>