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

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

No comments:

Post a Comment