Blog Details

Group By and Having Clause


                                
                                    <h2><strong>* Group By :-</strong></h2>
<ul>
	<li><strong>Aggregate functions</strong> often need an added <strong>GROUP BY statement</strong>.</li>
	<li>The GROUP BY statement is <strong>used</strong> in conjunction with the aggregate functions to g<strong>roup the result-set by one or more columns.</strong></li>
	<li>In standard SQL, a query that includes a <code>GROUP BY</code> clause <strong>cannot</strong> refer to<strong> nonaggregated columns</strong> in the select list that are not named in the <code>GROUP BY</code> clause.</li>
</ul>
&nbsp;
<h3><strong>Syntax :</strong></h3>
<div>SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;</div>
<div></div>
<div></div>
<div></div>
<h3><strong>Example :</strong></h3>
<pre>SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;
</pre>
<h2>* Group By (Aggregate) Functions :-</h2>
<table summary="Aggregate (GROUP BY)&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;
Functions" border="1"><colgroup> <col /> <col /></colgroup>
<thead>
<tr>
<th scope="col">NAME</th>
<th scope="col">DESCRIPTION</th>
</tr>
</thead>
<tbody>
<tr>
<td scope="row"><code>AVG()</code></td>
<td>Return the average value of the argument</td>
</tr>
<tr>
<td scope="row"><code>BIT_AND()</code></td>
<td>Return bitwise and</td>
</tr>
<tr>
<td scope="row"><code>BIT_OR()</code></td>
<td>Return bitwise or</td>
</tr>
<tr>
<td scope="row"><code>BIT_XOR()</code></td>
<td>Return bitwise xor</td>
</tr>
<tr>
<td scope="row"><code>COUNT(DISTINCT)</code></td>
<td>Return the count of a number of different values</td>
</tr>
<tr>
<td scope="row"><code>COUNT()</code></td>
<td>Return a count of the number of rows returned</td>
</tr>
<tr>
<td scope="row"><code>GROUP_CONCAT()</code></td>
<td>Return a concatenated string</td>
</tr>
<tr>
<td scope="row"><code>MAX()</code></td>
<td>Return the maximum value</td>
</tr>
<tr>
<td scope="row"><code>MIN()</code></td>
<td>Return the minimum value</td>
</tr>
<tr>
<td scope="row"><code>STD()</code></td>
<td>Return the population standard deviation</td>
</tr>
<tr>
<td scope="row"><code>STDDEV_POP()</code></td>
<td>Return the population standard deviation</td>
</tr>
<tr>
<td scope="row"><code>STDDEV_SAMP()</code></td>
<td>Return the sample standard deviation</td>
</tr>
<tr>
<td scope="row"><code>STDDEV()</code></td>
<td>Return the population standard deviation</td>
</tr>
<tr>
<td scope="row"><code>SUM()</code></td>
<td>Return the sum</td>
</tr>
<tr>
<td scope="row"><code>VAR_POP()</code></td>
<td>Return the population standard variance</td>
</tr>
<tr>
<td scope="row"><code>VAR_SAMP()</code></td>
<td>Return the sample variance</td>
</tr>
<tr>
<td scope="row"><code>VARIANCE()</code></td>
<td>Return the population standard variance</td>
</tr>
</tbody>
</table>
<h2></h2>
<h2>* <strong>HAVING Clause:-</strong></h2>
<ul>
	<li>The HAVING clause was <strong>added</strong> to SQL because the <strong>WHERE keyword</strong> could not be <strong>used</strong> with <strong>aggregate functions.</strong></li>
	<li>The MySQL HAVING clause is used in the <strong>SELECT STATEMENT</strong> to specify filter conditions for group of rows or aggregates.</li>
	<li>The MySQL HAVING clause is often <strong>used</strong> with the<strong> GROUP BY clause.</strong></li>
	<li>When using with the GROUP BY clause, you can apply a <strong>filter condition</strong> to the columns that appear in the GROUP BY clause.</li>
</ul>
&nbsp;
<h3>      <strong>Syntax :</strong></h3>
<div>SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;</div>
<div></div>
<div></div>
<div>
<h3><strong>Example 1:</strong></h3>
<pre>SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;
</pre>
</div>
<div>
<h3><strong>Example 2:</strong></h3>
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) &gt; 10;

</div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<h2><strong> </strong></h2>
                                    


Categories

Php mysql

Latest posts

13.05.14
Implementing CRUD Operations ¶

<ul> <li>Now comes the fun part. We would like t

13.05.14
View

<ol> <li><a href="http://www.yiiframework.com/do

13.05.14
Creating Model

<ol> <li><a href="http://www.yiiframework.com/do

Get Free Quote