**where**,

**Having**,

**Group**

**By**and the aggregations like

**Count**,

**Sum**and

**Avg**.

First we will have a simple and a small table ‘order’ with data’s as follows,

ORDER STATE AMOUNT

------+-------+-------------

1234 NY 10.00

1235 TX 15.00

1236 CA 20.00

1237 TX 25.00

1238 CA 30.00

1237 NY 35.00

1238 NY 40.00

**Using Aggregation:**

Aggregation combines rows together and performs some operation on their combined values. Very common aggregations are

**COUNT**,

**SUM**, and

**AVG**.

**SELECT COUNT(*) as count,SUM(amount) as sum,AVG(amount) as avg FROM orders**

this query will return as,

count sum avg

-----+------+-----

7 175 25

**Using Where Clause:**

If we need to get result with the basis of any state, use the following query.

**SELECT COUNT(*) as count,SUM(amount) as sum,AVG(amount) as avg FROM orders WHERE state = 'NY'**

this will return as,

count sum avg

----+------+----------

3 85 28.33333

As from the above results, there is the average has a repeating decimal. So we can use the

**Round**function.

**Using Group By Clause:**

The

**GROUP BY**clause says that the aggregations should be performed for the distinct values of a column or columns.

**SELECT state, COUNT(*) as count,SUM(amount) as sum, ROUND(AVG(amount),0) as avg FROM orders GROUP BY state**

This will return as,

STATE count sum avg

------+-----+------+----

NY 3 85 28

TX 2 40 20

CA 2 50 25

**Using Having Clause:**

The

**HAVING**clause lets us put a filter on the results after the aggregation has taken place. If we need to know which state having average amount of Rs.25 or more, use the following code.

**SELECT state, COUNT(*) as count,SUM(amount) as sum, ROUND(AVG(amount),0) as avg FROM orders GROUP BY state HAVING AVG(amount) >= 25**

Result of this query,

state count sum avg

------+-----+------+----

NY 3 85 28

CA 2 50 25

Now we can pull results out of a database in a single query with the use of combine the

**WHERE**,

**GROUP BY**, and

**HAVING**as follows,

**SELECT state, COUNT(*),SUM(amount) as sum, ROUND(AVG(amount),0) as avg FROM orders WHERE amount > 20 GROUP BY state HAVING avg(amount) >= 30**

That's it....

**...S.VinothkumaR.**

## No comments:

Post a Comment