SQL Group By Examples

SQL Group By Examples

Example 1 of SQL Group By

Let us say we have a table name Orders.

Orders (O_Id, OrderDate, OrderPrice, Customer)

we want to find the total sum (total order) of each customer.

SELECT Customer,SUM(OrderPrice)
FROM Orders
GROUP BY Customer

Source: http://www.w3schools.com/sql/sql_groupby.asp


Example 2 of SQL Group By

Let us say we have a table name Sales.

Sales(OrderID, OrderDate, OrderPrice, OrderQuantity, CustomerName)

We want to retrieve a list with unique customers from our Sales table, and at the same time to get the total amount each customer has spent in our store.

SELECT CustomerName, SUM(OrderPrice)
FROM Sales
GROUP BY CustomerName

Source: http://www.sql-tutorial.com/sql-group-by-sql-tutorial/


Example 3 of SQL Group By

Returns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.

SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID

Source: http://en.wikipedia.org/wiki/Group_by_(SQL)


Example 4 of SQL Group By

From Sells(bar, beer, price) find the average price for each beer

SELECT beer, AVG(price)
FROM Sells
GROUP BY beer;

Source: infolab.stanford.edu/~ullman/fcdb/aut07/slides/ra-sql2.ppt


Example 5 of SQL Group By

You could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Source: http://www.techonthenet.com/sql/group_by.php

SQL Group By

SQL Having

Examples of SQL Group By and SQL Having

Null in SQL Group By

It is a clause in SQL, which specifies how to report the output of the query. Allows one to define a subset of the values of a particular field and to apply an aggregate function to the subsets.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc).

Example 1 of SQL Group BY

Calculate the total sales for each store in the following table

store_name	Sales	Date
London	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
London	$300	Jan-08-1999
Boston	$700	Jan-08-1999

First, we need to make sure we select the store name as well as total sales.

SELECT store_name, SUM (Sales)
FROM Store_Information

Second, we need to make sure that all the sales figures are grouped by stores.

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

So the final query is:

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

The result is:

store_name	SUM(Sales)
London	$1800
San Diego	$250
Boston	$700

Example 2 of SQL Group BY

SELECT COUNT (*) FROM t_state

The above statement returns the total number of rows in the table. We can use GROUP BY to count the number of offices in each state.

With GROUP BY, the table is split into groups by state, and COUNT (*) is applied to each group in turn.

SELECT state, COUNT (*)
FROM t_state
GROUP BY state;

Important points to remember:

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department, count (*) as cnt
From emp
Group by department

Correct way is:

Select deptno as department, count (*) as cnt
From emp
Group by deptno


What is the difference between the outputs of the following two queries?

Statement 1:

SELECT COUNT (*), SUM (comm)
FROM hr.employees;

Statement 2:

SELECT COUNT (comm), SUM (comm)
FROM hr.employees;

The COUNT (*) will count all rows in the table.

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

Restriction on SELECT Lists with Aggregation

If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.

Multiple Choice Questions - SQL HAVING / GROUP BY

Multiple Choice Questions - SQL HAVING / GROUP BY

1. Having clause is processed after the GROUP BY clause and any aggregate functions.

A) True
B) False

2. In the context of MS SQL SERVER, with the exception of ............ column(s), any column can participate in the GROUP BY clause.

A) bit
B) text
C) ntext
D) image
E) All of above

3. The sequence of the columns in a GROUP BY clause has no effect in the ordering of the output.

A) True
B) False

4. You want all dates when any employee was hired. Multiple employees were hired on the same date and you want to see the date only once.

Query - 1

Select distinct hiredate
From hr.employee
Order by hiredate;

Query - 2

Select hiredate
From hr.employees 
Group by hiredate
Order by hiredate;

Which of the above query is valid?

A) Query - 1
B) Query - 2
C) Both

5. GROUP BY ALL generates all possible groups - even those that do not meet the query's search criteria.

A) True
B) False

6. All aggregate functions ignore NULLs except for ............

A) Distinct
B) Count (*)
C) Average()
D) None of above

7. Using GROUP BY ............ has the effect of removing duplicates from the data.

A) with aggregates
B) with order by
C) without order by
D) without aggregates

8. Below query is run in SQL Server 2012, is this query valid or invalid:

Select count(*) as X
from Table_Name
Group by ()

A) Valid
B) Invalid

9. For the purposes of ............, null values are considered equal to other nulls and are grouped together into a single result row.

A) Having
B) Group By
C) Both of above
D) None of above

10. If you SELECT attributes and use an aggregate function, you must GROUP BY the non-aggregate attributes.

A) True
B) False

Answers

1) a, 2) e, 3) b, 4) c, 5) a, 6) b, 7) d, 8) a, 9) b, 10) a