SQL Server COUNT() 和SUM()的使用方法

 

根据BOL中解释COUNT用来返回组中的项数。

COUNT(*)返回组中的项数。包括 NULL 值和重复项

COUNT(ALL expression)：对组中的每一行都计算 expression 并返回非空值的数量。默认值。

COUNT(DISTINCT expression)：对组中的每一行都计算 expression 并返回唯一非空值的数量。

 

 

ID          Value

----------- --------------------------------------------------

1           A

2           B

2           B

3           B

4           NULL

 

 

select COUNT(* ) from Table_1

 

-----------

5

 

SELECT COUNT(Value) FROM Table_1

-----------

4

 

SELECT COUNT(DISTINCT Value) FROM Table_1

-----------

2

 

 

SUM返回表达式中所有值的和或仅非重复值的和。SUM 只能用于数字列。空值将被忽略

ID          Value

----------- -----------

1           1

2           2

3           3

4           4

5           NULL

6           5

7           5

 

SELECT SUM(Value) From Table_1

 

-----------

20

 

SELECT SUM(DISTINCT Value) From Table_1

 

-----------

15

 

以Northwind数据库中的Employees，Orders表为例，列出所有雇员的姓名以及其拥有的Order的个数。分别用Count()和SUM（）实现。

 

SELECT e.EmployeeID,FirstName,LastName,Count(O.EmployeeID) AS OrderCount

FROM dbo.Employees AS E LEFT JOIN dbo.Orders AS O

ON E.EmployeeID = O.EmployeeID

Group By E.EmployeeID,FirstName,LastName

 

 

 

SELECT e.EmployeeID,FirstName,LastName,SUM(CASE WHEN O.EmployeeID IS NULL THEN 0 ELSE 1 END) AS OrderCount

FROM dbo.Employees AS E LEFT JOIN dbo.Orders AS O

ON E.EmployeeID = O.EmployeeID

Group By E.EmployeeID,FirstName,LastName

 

注意的是这里一定不能用COUNT（*），因为COUNT（*）是计算行数，对于没有定单的用户也是为1。只有在JOIN的情况下，COUNT（*）才等效于Count(O.EmployeeID

 

 

附：这两个表的定义。

USE [Northwind]

GO

 

/****** Object:  Table [dbo].[Employees]    Script Date: 01/13/2012 22:57:44 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[Employees](

      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

      [LastName] [nvarchar](20) NOT NULL,

      [FirstName] [nvarchar](10) NOT NULL,

      [Title] [nvarchar](30) NULL,

      [TitleOfCourtesy] [nvarchar](25) NULL,

      [BirthDate] [datetime] NULL,

      [HireDate] [datetime] NULL,

      [Address] [nvarchar](60) NULL,

      [City] [nvarchar](15) NULL,

      [Region] [nvarchar](15) NULL,

      [PostalCode] [nvarchar](10) NULL,

      [Country] [nvarchar](15) NULL,

      [HomePhone] [nvarchar](24) NULL,

      [Extension] [nvarchar](4) NULL,

      [Photo] [image] NULL,

      [Notes] [ntext] NULL,

      [ReportsTo] [int] NULL,

      [PhotoPath] [nvarchar](255) NULL,

 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

(

      [EmployeeID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])

REFERENCES [dbo].[Employees] ([EmployeeID])

GO

 

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]

GO

 

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [CK_Birthdate] CHECK  (([BirthDate] < getdate()))

GO

 

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]

GO

 

 

USE [Northwind]

GO

 

/****** Object:  Table [dbo].[Orders]    Script Date: 01/13/2012 23:02:09 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[Orders](

      [OrderID] [int] IDENTITY(1,1) NOT NULL,

      [CustomerID] [nchar](5) NULL,

      [EmployeeID] [int] NULL,

      [OrderDate] [datetime] NULL,

      [RequiredDate] [datetime] NULL,

      [ShippedDate] [datetime] NULL,

      [ShipVia] [int] NULL,

      [Freight] [money] NULL,

      [ShipName] [nvarchar](40) NULL,

      [ShipAddress] [nvarchar](60) NULL,

      [ShipCity] [nvarchar](15) NULL,

      [ShipRegion] [nvarchar](15) NULL,

      [ShipPostalCode] [nvarchar](10) NULL,

      [ShipCountry] [nvarchar](15) NULL,

 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED

(

      [OrderID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[Orders]  WITH NOCHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])

REFERENCES [dbo].[Customers] ([CustomerID])

GO

 

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]

GO

 

ALTER TABLE [dbo].[Orders]  WITH NOCHECK ADD  CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([EmployeeID])

REFERENCES [dbo].[Employees] ([EmployeeID])

GO

 

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employees]

GO

 

ALTER TABLE [dbo].[Orders]  WITH NOCHECK ADD  CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([ShipVia])

REFERENCES [dbo].[Shippers] ([ShipperID])

GO

 

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Shippers]

GO

 

ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_Freight]  DEFAULT (0) FOR [Freight]

GO