# 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.

## 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:

 customer Total Items A+Maintenance 14 Gerald Garner 5 Tizag 23

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:

 product Total Items 19" LCD Screen 10 Hanging Files 25 HP Printer 4 Stapler 3

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:

 product COST A+Maintenance 209.86 Gerals Garner 899.95 Tizag 1448.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_order product Total 2008-07-25 00:00:00.000 19" LCD Screen 5 2008-07-25 00:00:00.000 HP Printer 4 2008-08-01 00:00:00.000 Hanging Files 11 2008-08-01 00:00:00.000 Stapler 3 2008-08-15 00:00:00.000 19" LCD Screen 5 2008-08-16 00:00:00.000 Hanging Files 14

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_order product Total 2008-08-01 00:00:00.000 Hanging Files 11 2008-08-16 00:00:00.000 Hanging Files 14

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).

# Found Something Wrong in this Lesson?

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

 Web Reference HTML Reference CSS Reference CSS Examples PHP Examples Help Tizag Grow Link to Tizag Tizag.com Store
 Advertise Here More Tutorials! Microsoft Office Tutorials Artist Tutorials