Kamis, 30 Agustus 2012

sql select count

SQL Server COUNT() function use to get number of rows that matches a specified criteria.

Example 1:
select COUNT(ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35)
)v0

#Results:
NumOfItem
6

Example 2:
select COUNT(DISTINCT ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35)
)v1

#Results:
NumOfItem
2

Example 3:
See following examples and compare with previous query:


select COUNT(DISTINCT ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35) union

    select (null),('P0003'),1
)v2

#Results:
NumOfItem
2


Notes:
SQL Server DISTINCT sintax added to get number of unique item name
Results Example 2 and 3  same couse NULL values will not be counted

Tidak ada komentar:

Posting Komentar