數(shù)據(jù)庫中sum的用法
數(shù)據(jù)庫中sum的用法
數(shù)據(jù)庫中sum的用法的用法你知道嗎？下面小編就跟你們詳細(xì)介紹下數(shù)據(jù)庫中sum的用法的用法，希望對你們有用。
數(shù)據(jù)庫中sum的用法的用法如下：
根據(jù)BOL中解釋COUNT用來返回組中的項(xiàng)數(shù)。
COUNT(*)返回組中的項(xiàng)數(shù)。包括 NULL 值和重復(fù)項(xiàng)
COUNT(ALL expression)：對組中的每一行都計算 expression 并返回非空值的數(shù)量。默認(rèn)值。
COUNT(DISTINCT expression)：對組中的每一行都計算 expression 并返回唯一非空值的數(shù)量。
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返回表達(dá)式中所有值的和或僅非重復(fù)值的和。SUM 只能用于數(shù)字列。空值將被忽略
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數(shù)據(jù)庫中的Employees，Orders表為例，列出所有雇員的姓名以及其擁有的Order的個數(shù)。分別用Count()和SUM（）實(shí)現(xiàn)。
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（*），因?yàn)镃OUNT（*）是計算行數(shù)，對于沒有定單的用戶也是為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