数据库中sum函数 数据库中sum的用法

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

数据库中sum函数 数据库中sum的用法

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

  

爱华网本文地址 » http://www.aihuau.com/a/254361/354572723.html

更多阅读

Oracle中的NVL函数 oracle decode函数

Oracle中函数以前介绍的字符串处理,日期函数,数学函数,以及转换函数等等,还有一类函数是通用函数。主要有:NVL,NVL2,NULLIF,COALESCE,这几个函数用在各个类型上都可以。下面简单介绍一下几个函数的用法。在介绍

转载 python中的reduce python中的reduce函数

原文地址:python中的reduce作者:蒙奇奇python中的reduce内建函数是一个二元操作函数,他用来将一个数据集合(链表,元组等)中的所有数据进行下列操作:用传给reduce中的函数func()(必须是一个二元操作函数)先对集合中的第1,2个数据进行操作,得到的

声明:《数据库中sum函数 数据库中sum的用法》为网友伯贤快说分享!如侵犯到您的合法权益请联系我们删除