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

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