How to sum a column to existing sql query
SqlCommand scz = new SqlCommand(" SELECT Datename(dw, Time1)+', '+CONVERT(VARCHAR(12), Time1, 107) as Query_Date, (SELECT COUNT(Exact*) FROM MealLog as c WHERE c.Time1= clog.Time1 AND Company='NAND') AS NAND,N_MCOST=SUM(CASE WHEN Company='NAND' THEN 1 ELSE 0 END)* MAX(CASE WHEN Company='NAND' THEN Value ELSE 0 END) FROM MealLog AS clog WHERE Time1 >= '" + (Session["D"].ToString()) + "' and Time1 <= '" + (Session["D1"].ToString()) + "' GROUP BY Time1 ORDER BY Time1 ASC ", dbConn);
Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.
CREATE TABLE [dbo].[MealLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[Emp_No] [nvarchar](250) NOT NULL,
[Meal_Cat] [nvarchar](250) NOT NULL,
[Department] [nvarchar](250) NOT NULL,
[Company] [nvarchar](250) NOT NULL,
[Reciept_No] [int] NOT NULL,
[Value] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Time1] [date] NOT NULL,
[Exact] [int] NULL,
CONSTRAINT [PK_MealLog] PRIMARY KEY CLUSTERED
(
[id] 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
SET IDENTITY_INSERT [dbo].[MealLog] ON
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1, N'Halland', N'67891', N'2X', N'ENGINEERING', N'LOG', 10000327, 300, CAST(N'2018-06-24T16:20:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2, N'Halland', N'67892', N'2X', N'ENGINEERING', N'LOG', 10000328, 300, CAST(N'2018-06-24T16:20:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3, N'Halland', N'67893', N'2X', N'ENGINEERING', N'LOG', 10000329, 300, CAST(N'2018-06-24T16:20:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (4, N'Halland', N'67894', N'2X', N'ENGINEERING', N'ARREY', 10000330, 500, CAST(N'2018-06-24T16:21:00.000' AS DateTime), CAST(N'2020-12-02' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (5, N'Halland', N'67895', N'2X', N'ENGINEERING', N'KEEGEE', 10000331, 500, CAST(N'2018-06-24T16:21:00.000' AS DateTime), CAST(N'2020-12-03' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (6, N'Halland', N'67896', N'2X', N'ENGINEERING', N'NAND', 10000332, 600, CAST(N'2020-12-03T16:21:00.000' AS DateTime), CAST(N'2020-12-03' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1002, N'Halland', N'67897', N'2X', N'ENGINEERING', N'NAND', 10000333, 600, CAST(N'2018-06-25T18:02:00.000' AS DateTime), CAST(N'2020-12-04' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1004, N'Tyrese Test', N'12348', N'X', N'PRODUCTION', N'NAND', 10000334, 600, CAST(N'2020-12-07T00:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1005, N'Tyrese Test', N'12349', N'X', N'PRODUCTION', N'NAND', 10000335, 600, CAST(N'2020-12-07T00:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1006, N'Tyrese Test', N'12341', N'X', N'PRODUCTION', N'NAND', 10000336, 600, CAST(N'2020-12-07T00:25:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1007, N'Tyrese Test', N'12342', N'X', N'PRODUCTION', N'NAND', 10000337, 600, CAST(N'2020-12-07T00:27:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1008, N'Tyrese Test', N'12343', N'X', N'PRODUCTION', N'NAND', 10000338, 600, CAST(N'2020-12-07T00:30:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1009, N'Tyrese Test', N'12344', N'X', N'PRODUCTION', N'NAND', 10000339, 600, CAST(N'2020-12-07T00:30:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1010, N'Tyrese Test', N'12345', N'X', N'PRODUCTION', N'NAND', 10000340, 600, CAST(N'2020-12-07T00:31:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1011, N'Tyrese Test', N'12346', N'X', N'PRODUCTION', N'NAND', 10000341, 600, CAST(N'2020-12-07T00:32:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1012, N'Tyrese Test', N'12347', N'X', N'PRODUCTION', N'NAND', 10000342, 600, CAST(N'2020-12-07T00:36:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1013, N'Tyrese Test', N'12348', N'X', N'PRODUCTION', N'NAND', 10000343, 600, CAST(N'2020-12-07T00:39:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (1014, N'Tyrese Test', N'12349', N'X', N'PRODUCTION', N'NAND', 10000344, 600, CAST(N'2020-12-07T00:42:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2004, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000345, 600, CAST(N'2020-12-07T13:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2005, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000346, 600, CAST(N'2020-12-07T13:23:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2006, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000347, 600, CAST(N'2020-12-07T13:24:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2007, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000348, 600, CAST(N'2020-12-07T13:28:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2008, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000349, 600, CAST(N'2020-12-07T13:56:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2009, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000350, 600, CAST(N'2020-12-07T13:57:00.000' AS DateTime), CAST(N'2020-12-07' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2010, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000351, 600, CAST(N'2020-12-07T13:59:00.000' AS DateTime), CAST(N'2021-10-29' AS Date), 1)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2011, N'Tyrese Test', N'1234', N'X', N'PRODUCTION', N'NAND', 10000352, 600, CAST(N'2020-12-07T14:14:00.000' AS DateTime), CAST(N'2021-10-30' AS Date), 1)
SET IDENTITY_INSERT [dbo].[MealLog] OFF
GO
PLEASE HELP
