Senin, 03 September 2012

having in sql

SQL Server Having Clause Statement:
SELECT ColumnName, aggregate_function(ColumnName) FROM TableName
WHERE ColumnName operator value GROUP BY ColumnName
HAVING aggregate_function(ColumnName) operator valu

Example 1:
 select CustName,ProductName,SUM(Qty)Quantity from (
    select ('Buana Jaya')CustName,('Tubex')ProductName,(10)Qty union
    select ('Citra Makmur'),('Tubex'),(15) union
    select ('Citra Makmur'),('Dengue'),(5) union
    select ('Buana Jaya'),('Tubex'),(5) union
    select ('Citra Makmur'),('Tubex'),(25)
)v0 group by CustName,ProductName having CustName='Citra Makmur'

#Results:
CustomerName    ProductName    Quantity
Citra Makmur    Dengue         5
Citra Makmur    Tubex          40

On Example 1, query can modified like show on Example 2 to make same result.

Example 2:
select CustName,ProductName,SUM(Qty)Quantity from (
    select ('Buana Jaya')CustName,('Tubex')ProductName,(10)Qty union
    select ('Citra Makmur'),('Tubex'),(15) union
    select ('Citra Makmur'),('Dengue'),(5) union
    select ('Buana Jaya'),('Tubex'),(5) union
    select ('Citra Makmur'),('Tubex'),(25)
)v0 where CustName='Citra Makmur' group by CustName,ProductName

#Results:
CustomerName    ProductName    Quantity
Citra Makmur    Dengue         5
Citra Makmur    Tubex          40

Notes:
Example 1 use combine group by having clause sql server statement while
Example 2 use combine where group by sql server statement

Tidak ada komentar:

Posting Komentar