sql having

SQL Functions

homeBack

Next

SQL HAVING

  • The HAVING Clause

    The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

    SQL HAVING 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

  • SQL GROUP BY Example

    We have the following “Orders” table:

    OrderID Date Price Customer
    1 2012/12/12 1000 Joy
    2 2012/12/16 1600 Sam
    3 2012/12/22 1700 Arjun
    4 2012/12/23 1300 Joy
    5 2012/12/30 2300 John
    6 2012/12/31 1400 Joy

    Now we want to find if any of the customers have a total order of more than 3000.

    We use the following SQL statement:

    SELECT Customer,SUM(Price) FROM Orders
    GROUP BY Customer
    HAVING SUM(Price)>3000

    The result-set will look like this:

    Customer SUM(Price)
    Joy 3700

    Now we want to find if the customers “Hansen” or “Jensen” have a total order of more than 1500.

    We add an ordinary WHERE clause to the SQL statement:

    SELECT Customer,SUM(Price) FROM Orders
    WHERE Customer=’Arjun’ OR Customer=’Joy’
    GROUP BY Customer
    HAVING SUM(Price)>1400

    The result-set will look like this:

    Customer SUM(Price)
    Joy 3700
    Arjun 1700

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s