- OrderID – a numeric value uniquely identifying each order. This field is the primary key for the database.
- Salesperson – a text value providing the name of the salesperson who sold the products. This field is a foreign key to another table containing personnel information.
- CustomerID – a numeric value corresponding to a customer account number. This field is also a foreign key, referencing a table containing customer account information.
- Revenue – a numeric value corresponding to the dollar amount of the sale.
This would retrieve all records from the database corresponding to sales made by Jim:
SELECT * FROM Orders WHERE Salesperson LIKE ‘Jim’
You could review this information and perform some manual calculations to come up with performance statistics, but this would be a tedious task that you would have to repeat for each salesperson in the company. Instead, you can replace this work with a single GROUP BY query that calculates statistics for each salesperson in the company. You simply write the query and specify that the database should group the results based upon the Salespeson field. You may then use any of the SQL aggregate functions to perform calculations on the results.
OrderID Salesperson CustomerID Revenue 12482 Jim 182 40000 12488 Jim 219 25000 12519 Jim 137 85000 12602 Jim 182 10000 12741 Jim 155 90000
Here’s an example. If you executed the following SQL statement:
You would get the following results:
SELECT Salesperson, SUM(Revenue) AS ‘Total’, MIN(Revenue) AS ‘Smallest’, MAX(Revenue) AS ‘Largest’, AVG(Revenue) AS ‘Average’, COUNT(Revenue) AS ‘Number’ FROM Orders GROUP BY Salesperson
As you can see, this powerful function allows you to generate small reports from within a SQL query, providing valuable business intelligence to the manager conducting the performance reviews. The GROUP BY clause is often used in databases for this purpose and is a valuable tool in the DBA’s bag of tricks.
Salesperson Total Smallest Largest Average Number Jim 250000 10000 90000 50000 5 Mary 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3