数据库中sum的用法的用法你知道吗？下面小编就跟你们详细介绍下数据库中sum的用法的用法，希望对你们有用。

数据库中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