GROUP BY Clause
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Example - Suppose you want to check number of orders placed by each customer
Here, above we have 2 tables customer and order and we need to find number of orders by each customer.
Query - select c.name, c.phone from customer c join orders o on c.id = o.custId
Now, To get the count of repeated customers
Query - select c.name, count(o.custid) as totalorders from
customer c join orders o on c.id = o.custId
group by c.name
Multiple columns in Group By
Query - select c.name, c.phone, count(o.custid) as totalorders from
customer c join orders o on c.id = o.custId
group by c.name, c.phone
HAVING Clause
There is always some situation where you need to use aggregate function with WHERE clause. Using aggregate function with WHERE clause is not allowed. So with the help of HAVING clause we can use aggregate function with WHERE clause.
For example as above in GROUP BY clause if we would like to get those records having more than 1 order taht can be achived by using HAVING with WHERE clause.
GROUP BY Query as above to get customer detail and total number of oders placed by them
Query - select c.name, c.phone, count(o.custid) as totalorders from
customer c join orders o on c.id = o.custId
group by c.name, c.phone
Using HAVING clause to get customer having more than 4 orders placed
Query - select c.name, c.phone, count(o.custid) as totalorders from
customer c join orders o on c.id = o.custId
group by c.name, c.phone
having count(o.custid) > 4
Same using where clause
Query - select c.name, c.phone, count(o.custid) as totalorders from
customer c join orders o on c.id = o.custId
where c.name = 'tarran'
group by c.name, c.phone
having count(o.custid) > 4
