GROUP BY, HAVING, SUM, AVG, and COUNT(*)

Here is the simple example of using the clauses like 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.