sql server group by

SQL Server Group by Statement
SELECT ColumnName, aggregate_function(ColumnName) FROM TableName
WHERE ColumnName operator value GROUP BY ColumnName
HAVING aggregate_function(ColumnName) operator value

Group by Statemet often used in conjunction with a having clause statement.

Example 1:
select Catagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0

#Results:
Catagory
Fish
Bird
Bird
Fish
Bird

Example 2:
Still use query on Example 1, with added group by statement.
 select Catagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0 group by Catagory

#Results:
Catagory
Bird
Fish

Example 3:
Still use query on Example 2, with added SQL Server COUNT() Function to get Count of Similiar Catagory.
select Catagory, COUNT(Catagory)NumOfCatagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0 group by Catagory

#Results:
Catagory    NumOfCatagory
Bird        3
Fish        2

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