Please i have this report, it is giving me this error
Column 'MealLog.Exact' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
My Code
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'CRIMETECHBULK', N'CRIMETECHBULK', N'1', N'CLEANING', N'ARPN', 10000368, 600, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 200)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2, N'CRIMETECHBULK', N'CRIMETECHBULK', N'1', N'CLEANING', N'ARPNBULK', 10000369, 600, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 300)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3, N'CRIMETECHBULK', N'CRIMETECHBULK', N'1', N'CLEANING', N'DELOG', 10000370, 400, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 400)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (4, N'CRIMETECHBULK', N'CRIMETECHBULK', N'1', N'CLEANING', N'DELOGBULK', 10000371, 400, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 500)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (5, N'CRIMETECHBULK', N'CRIMETECHBULK', N'1', N'CLEANING', N'RDELOG', 10000372, 400, CAST(N'2021-12-19T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 600)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (7, N'ARPNBULK', N'ARPNBULK', N'1', N'CLEANING', N'CRIMETECH', 10000374, 400, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 700)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (8, N'DELOGBULK', N'DELOGBULK', N'1', N'CLEANING', N'TUSKEEGEE', 10000375, 400, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 800)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (9, N'DELOGBULK', N'DELOGBULK', N'1', N'CLEANING', N'TUSKEEGEEEBULK', 10000376, 400, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 900)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (10, N'DELOGBULK', N'DELOGBULK', N'1', N'CLEANING', N'VISITOR', 10000377, 600, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 100)
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (11, N'ARPNBULK', N'ARPNBULK', N'1', N'CLEANING', N'MOBOL', 10000378, 400, CAST(N'2021-12-15T00:00:00.000' AS DateTime), CAST(N'2021-12-21' AS Date), 50)
SET IDENTITY_INSERT [dbo].[MealLog] OFF
<%
SqlCommand scz = new SqlCommand("DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) SET @DynamicPivotQuery =N';WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ''S/N'', Datename(dw, Time1)+'', '' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE, ARPN, ARPN * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''ARPN'') ARPNCOST, ARPNBULK, ARPNBULK * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''ARPNBULK'') ARPNBULKCOST,AGGREY, AGGREY * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''AGGREY'') AGGREYCOST,CRIMETECH, CRIMETECH * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''CRIMETECH'') CRIMETECHCOST ,DELOG, DELOG * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''DELOG'') DELOGCOST,DELOGBULK, DELOGBULK * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''DELOGBULK'') DELOGBULKCOST,RDELOG, RDELOG * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''RDELOG'') RDELOGCOST ,TUSKEEGEE, TUSKEEGEE * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''TUSKEEGEE'') TUSKEEGEECOST,TUSKEEGEEBULK, TUSKEEGEEBULK * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''TUSKEEGEEBULK'') TUSKEEGEEBULKCOST,VISITOR, VISITOR * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''VISITOR'') VISITORCOST,MOBOL, MOBOL * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''MOBOL'') MOBOLCOST FROM (SELECT Time1,Exact,Company from MealLog WHERE Time1 >= ''" + (Session["D"].ToString()) + "'' and Time1 <= ''" + (Session["D1"].ToString()) + "'' GROUP BY Time1,Company) t pivot ( MAX(Exact) FOR Company IN ([ARPN],[ARPNBULK],[AGGREY],[CRIMETECH],[DELOG],[DELOGBULK],[RDELOG],[TUSKEEGEE],[TUSKEEGEEBULK],[VISITOR],[MOBOL])) piv) SELECT CAST([S/N] AS VARCHAR(10)) [S/N],QUERYDATE, ISNULL(ARPN,0) ARPN,ISNULL(ARPNCOST,0) ARPNCOST, ISNULL(ARPNBULK,0) ARPNBULK,ISNULL(ARPNBULKCOST,0) ARPNBULKCOST,ISNULL(AGGREY,0) AGGREY,ISNULL(AGGREYCOST,0) AGGREYCOST,ISNULL(CRIMETECH,0) CRIMETECH,ISNULL(CRIMETECHCOST,0) CRIMETECHCOST,ISNULL(DELOG,0) DELOG,ISNULL(DELOGCOST,0) DELOGCOST,ISNULL(DELOGBULK,0) DELOGBULK,ISNULL(DELOGBULKCOST,0) DELOGBULKCOST,ISNULL(RDELOG,0) RDELOG,ISNULL(RDELOGCOST,0) RDELOGCOST,ISNULL(TUSKEEGEE,0) TUSKEEGEE,ISNULL(TUSKEEGEECOST,0) TUSKEEGEECOST,ISNULL(TUSKEEGEEBULK,0) TUSKEEGEEBULK,ISNULL(TUSKEEGEEBULKCOST,0) TUSKEEGEEBULKCOST,ISNULL(VISITOR,0) VISITOR,ISNULL(VISITORCOST,0) VISITORCOST,ISNULL(MOBOL,0) MOBOL,ISNULL(MOBOLCOST,0) MOBOLCOST FROM cte UNION SELECT ''TOTAL'','''',SUM(CAST(ISNULL(ARPN,0) AS BIGINT)),SUM(CAST(ISNULL(ARPNCOST,0) AS BIGINT)),SUM(CAST(ISNULL(ARPNBULK,0) AS BIGINT)),SUM(CAST(ISNULL(ARPNBULKCOST,0) AS BIGINT)),SUM(CAST(ISNULL(AGGREY,0) AS BIGINT)),SUM(CAST(ISNULL(AGGREYCOST,0) AS BIGINT)),SUM(CAST(ISNULL(CRIMETECH,0) AS BIGINT)),SUM(CAST(ISNULL(CRIMETECHCOST,0) AS BIGINT)),SUM(CAST(ISNULL(DELOG,0) AS BIGINT)),SUM(CAST(ISNULL(DELOGCOST,0) AS BIGINT)), SUM(CAST(ISNULL(DELOGBULK,0) AS BIGINT)),SUM(CAST(ISNULL(DELOGBULKCOST,0) AS BIGINT)),SUM(CAST(ISNULL(RDELOG,0) AS BIGINT)),SUM(CAST(ISNULL(RDELOGCOST,0) AS BIGINT)),SUM(CAST(ISNULL(TUSKEEGEE,0) AS BIGINT)),SUM(CAST(ISNULL(TUSKEEGEECOST,0) AS BIGINT)),SUM(CAST(ISNULL(TUSKEEGEEBULK,0) AS BIGINT)),SUM(CAST(ISNULL(TUSKEEGEEBULKCOST,0) AS BIGINT)),SUM(CAST(ISNULL(VISITOR,0) AS BIGINT)),SUM(CAST(ISNULL(VISITORCOST,0) AS BIGINT)),SUM(CAST(ISNULL(MOBOL,0) AS BIGINT)),SUM(CAST(ISNULL(MOBOLCOST,0) AS BIGINT)) FROM cte ' EXEC (@DynamicPivotQuery) ", dbConn);
scz.CommandType = CommandType.Text;
dbConn.Open();
SqlDataReader sdz = scz.ExecuteReader();
while (sdz.Read())
{
}
dbConn.Close();%>
please help make it work please
