How to write Sql or LinqToSql for this scenario?
A table has the following data:
Id UserName Price Date Status
1 Mike 2 2010-4-25 0:00:00 Success
2 Mike 3 2010-4-25 0:00:00 Fail
3 Mike 2 2010-4-25 0:00:00 Success
4 Lily 5 2010-4-25 0:00:00 Success
5 Mike 1 2010-4-25 0:00:00 Fail
6 Lily 5 2010-4-25 0:00:00 Success
7 Mike 2 2010-4-26 0:00:00 Success
8 Lily 5 2010-4-26 0:00:00 Fail
9 Lily 2 2010-4-26 0:00:00 Success
10 Lily 1 2010-4-26 0:00:00 Fail
I want to get the summary result from the data, the result should be:
UserName Date TotalPrice TotalRecord SuccessRecord FailRecord
Mike 2010-04-25 8 4 2 2
Lily 2010-04-25 10 2 2 0
Mike 2010-04-26 2 1 1 0
Lily 2010-04-26 8 3 1 2
The TotalPrice is the sum(Price) groupby UserName and Date
The TotalRecord is the count(*) groupby UserName and Date
The SuccessRecord is the count(*) groupby UserName and Date where Status='Success'
The FailRecord is the count(*) groupby UserName and Date where Status='Fail'
The TotalRecord = SuccessRecord + FailRecord
The sql server 2005 database script is:
/****** Object: Table [dbo].[Pay] Script Date: 04/28/2010 22:23:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pay]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Pay](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Price] [int] NULL,
[Date] [datetime] NULL,
[Status] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Pay] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Pay] ON
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (1, N'Mike', 2, CAST(0x00009D6300000000 AS DateTime), N'Success')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (2, N'Mike', 3, CAST(0x00009D6300000000 AS DateTime), N'Fail')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (3, N'Mike', 2, CAST(0x00009D6300000000 AS DateTime), N'Success')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (4, N'Lily', 5, CAST(0x00009D6300000000 AS DateTime), N'Success')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (5, N'Mike', 1, CAST(0x00009D6300000000 AS DateTime), N'Fail')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (6, N'Lily', 5, CAST(0x00009D6300000000 AS DateTime), N'Success')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (7, N'Mike', 2, CAST(0x00009D6400000000 AS DateTime), N'Success')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (8, N'Lily', 5, CAST(0x00009D6400000000 AS DateTime), N'Fail')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (9, N'Lily', 2, CAST(0x00009D6400000000 AS DateTime), N'Success')
INSERT [dbo].[Pay] ([Id], [UserName], [Price], [Date], [Status]) VALUES (10, N'Lily', 1, CAST(0x00009D6400000000 AS DateTime), N'Fail')
SET IDENTITY_INSERT [dbo].[Pay] OFF
