Bookmark and Share

SQL - Group By

SQL GROUP BY aggregates (consolidates and calculates) column values into a single record value. GROUP BY requires a list of table columns on which to run the calculations. At first, this behavior will resemble the SELECT DISTINCT command we toyed with earlier.

Advertise on Tizag.com

SQL Group By:

USE mydatabase;

SELECT customer
FROM orders
GROUP BY customer;

SQL Results:

customer
A+Maintenance
Gerald Garner
Tizag

Here, SQL has consolidated like values and returned those that are unique. In this case, we have actually duplicated the behavior of SELECT DISTINCT, but you have also seen firsthand how GROUP BY accepts a table column as a list and consolidates like customer values.

To unleash the true power of GROUP BY, it is necessary to include at least one mathematical (aggregate) function, and to do so we will utilize the SUM() function to calculate how many total items have been purchased by each of our customers.

SQL Code:

USE mydatabase;

SELECT customer, SUM(quantity) AS "Total Items"
FROM orders
GROUP BY customer;

SQL Results:

customerTotal Items
A+Maintenance14
Gerald Garner5
Tizag23

With the addition of the aggregate SUM() function, we've let SQL calculate how many products have been ordered by each customer and returned them for viewing with a single query statement.

Taking a look at another example, we can also figure out how many of each product was ordered with the use of a single query statement.

SQL Code:

USE mydatabase;

SELECT product, SUM(quantity) AS "Total Items"
FROM orders
GROUP BY product;

SQL Results:

productTotal Items
19" LCD Screen10
Hanging Files25
HP Printer4
Stapler3

GROUP BY would also be a great way to calculate how much total cash of our customers has spent. Let's take a look at what that query may look like.

SQL Code:

USE mydatabase;

SELECT customer,
  SUM((orders.quantity * inventory.price)) AS "COST"
FROM orders
JOIN inventory
ON orders.product = inventory.product
GROUP BY customer;

SQL Results:

productCOST
A+Maintenance209.86
Gerals Garner899.95
Tizag1448.77

SQL - Grouping By Multiple Columns

Like the ORDER BY clause, GROUP BY can accept a list of table columns on which to group by.

SQL Code:

USE mydatabase;

SELECT day_of_order,
  product,
  SUM(quantity) as "Total"
FROM orders
GROUP BY day_of_order,product
ORDER BY day_of_order;

SQL Results:

day_of_orderproductTotal
2008-07-25 00:00:00.00019" LCD Screen5
2008-07-25 00:00:00.000HP Printer4
2008-08-01 00:00:00.000Hanging Files11
2008-08-01 00:00:00.000Stapler3
2008-08-15 00:00:00.00019" LCD Screen5
2008-08-16 00:00:00.000Hanging Files14

This query will group together and sum the total number of products purchased on any given date, regardless of what customer has purchased the item. It's a very useful query to keep in mind.

SQL - Having

The SQL HAVING clause is "like a WHERE clause for aggregated data." It's used with conditional statements, just like WHERE, to filter results. One thing to note is that any column name appearing in the HAVING clause must also appear in the GROUP BY clause.

SQL Having:

USE mydatabase;

SELECT day_of_order,
  product,
  SUM(quantity) as "Total"
FROM orders
GROUP BY day_of_order,product,quantity
HAVING quantity > 7
ORDER BY day_of_order;

SQL Results:

day_of_orderproductTotal
2008-08-01 00:00:00.000Hanging Files11
2008-08-16 00:00:00.000Hanging Files14

The quantity column is now considered aggregated in SQL terms, because its values have been summed together using the SUM() function. In the example above, HAVING acts as the WHERE clause for aggregate values, filtering out results that do not meet the condition (quantity > 7).

Bookmark and Share




Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials