Search content within the blog

Friday, April 17, 2009

Group by and having clause in sqlserver

Group By clause

When Aggregate functions (like SUM) called it return the aggregate of all column values.It was impossible to find the sum for each individual group of column values so Group By clause used.

Syntax:

SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"

Let's following table "Store_Information"

Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005

To find total sales for each store:-

SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name

Output
Store_name SUM(Sales)
Los Angeles $1800
San Diego $250
Boston $700

The GROUP BY keyword is used when selecting multiple columns from tables and at least one arithmetic operator appears in the SELECT statement.

Having Clause

To limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is placed near the end of the SQL statement. SQL statement with the HAVING clause may or may not include the GROUP BY clause.
Syntax: Having Clause

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)

In our example, table "Store_Information" we would type.

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

Output
store_name SUM(Sales)
Los Angeles $1800

No comments:

Post a Comment