RSS Feed

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


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


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'


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;


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;


No comments:

Post a Comment