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