• Difference between Having and Where clause in SQL Server?

    Posted on August 21, 2012 by in Database, SQL Server

    HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. Usually Having clause is used to specify a search condition in a SQL statement which contains group by or some other aggregate condition. However WHERE clause is applied to individual rows and not to groups.

    Example 1:  Using Having Clause with GroupBy
     In this example, we will begin get list of customers and the total order amount

    SELECT TOP 5 (C.FirstName + ' ' + C.LastName) AS CUSTOMERNAME, SUM(SOH.SubTotal) AS TOTAL
    FROM Sales.SalesOrderHeader SOH
    INNER JOIN Person.Contact C ON C.ContactID = SOH.ContactID
    GROUP BY (C.FirstName + ' ' + C.LastName)
    
    CUSTOMERNAME TOTAL
    Martha Espinoza 77657.1436
    Donna Carreras 433942.3762
    Marshall Lal 3578.27
    Gabrielle Coleman 3578.27
    Madeline Mitchell 5962.34

    Lets say we are only interested in customers that have placed more than 50,000 in orders

    SELECT TOP 5 (C.FirstName + ' ' + C.LastName) AS CUSTOMERNAME, SUM(SOH.SubTotal) AS TOTAL
    FROM Sales.SalesOrderHeader SOH
    INNER JOIN Person.Contact C ON C.ContactID = SOH.ContactID
    GROUP BY (C.FirstName + ' ' + C.LastName)
    HAVING SUM(SOH.SubTotal) > 50000
    
    CUSTOMERNAME TOTAL
    Martha Espinoza 77657.1436
    Donna Carreras 433942.3762
    Reinout Hillmann 141111.5949
    Chris Ashton 225384.5324
    Carla Eldridge 63004.7151

    Example 2:  Using Having & Where Clauses with GroupBy
    You could use both having and where clause in same query. First where clause is applied and the rows that match the condition are grouped. Then the condition in the having clause is applied to the remaining rows.

    In below example, we would want to know get the list of customers who have placed orders worth more than 50,000 since year 2003.

    SELECT TOP 5 (C.FirstName + ' ' + C.LastName) AS CUSTOMERNAME, SUM(SOH.SubTotal) AS TOTAL
    FROM Sales.SalesOrderHeader SOH
    INNER JOIN Person.Contact C ON C.ContactID = SOH.ContactID
    WHERE OrderDate > '2003-01-01'
    GROUP BY (C.FirstName + ' ' + C.LastName)
    HAVING SUM(SOH.SubTotal) > 50000
    

    Difference between Having and Where clause 1

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    View all articles by

    Email : [email protected]

    Leave a Reply