Blog Details

Group By and Having Clause



                                

* Group By :-

  • Aggregate functions often need an added GROUP BY statement.
  • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
  • In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.
 

Syntax :

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

Example :

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

* Group By (Aggregate) Functions :-

NAME DESCRIPTION
AVG() Return the average value of the argument
BIT_AND() Return bitwise and
BIT_OR() Return bitwise or
BIT_XOR() Return bitwise xor
COUNT(DISTINCT) Return the count of a number of different values
COUNT() Return a count of the number of rows returned
GROUP_CONCAT() Return a concatenated string
MAX() Return the maximum value
MIN() Return the minimum value
STD() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
STDDEV() Return the population standard deviation
SUM() Return the sum
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance

HAVING Clause:-

  • The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
  • The MySQL HAVING clause is used in the SELECT STATEMENT to specify filter conditions for group of rows or aggregates.
  • The MySQL HAVING clause is often used with the GROUP BY clause.
  • When using with the GROUP BY clause, you can apply a filter condition to the columns that appear in the GROUP BY clause.
 

      Syntax :

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;

Example 1:

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

Example 2:

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) > 10;

 


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