All of Query was successfully tested and running well on Microsoft SQL Server 2008

Senin, 03 September 2012

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

Tidak ada komentar:

Posting Komentar