Hello there, I need your help.
This is a table [tbl_cco]
. Here you can find a demo of what I explain next
Sample DDL:
-- ----------------------------
-- Table structure for tbl_cco
-- ----------------------------
DROP TABLE IF EXISTS [dbo].[tbl_cco];
CREATE TABLE [dbo].[tbl_cco] (
[Total] int NULL,
[Total_X] int NULL,
[Total_Y] int NULL,
[Total_Z] int NULL,
[DateHour] datetime NULL,
[Cod] varchar(255) NULL
);
-- ----------------------------
-- Records of tbl_cco
-- ----------------------------
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'27', N'1', N'2', N'1', N'2025-06-01 09:00:00.000', N'PLRM');
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'22', N'5', N'3', N'0', N'2025-06-01 09:00:00.000', N'CTNA');
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'23', N'0', N'1', N'0', N'2025-06-01 21:00:00.000', N'PLRM');
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'18', N'3', N'1', N'0', N'2025-06-01 21:00:00.000', N'CTNA');
-- ----------------------------
-- Indexes structure for table tbl_cco
-- ----------------------------
CREATE UNIQUE NONCLUSTERED INDEX [CodCo]
ON [dbo].[tbl_cco] (
[DateHour] ASC,
[Cod] ASC
);
this table twice a day, at 9 in the morning and at 9 in the evening, extracts from a general detail table the total of the columns:
1. Total
2. Total_X
3. Total_Y
4. Total_Z
and for the codes
1. PLRM
2. CTNA
NOTE: The Total
column is not the sum of the individual columns
1. Total_X
2. Total_Y
2. Total_Z
Now I'm trying to get this output
But I can't get past this output
because if I try to select the date in the query it fails...
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The column 'cte.DateHour' is not valid in the select list because it is not included in an aggregate function or in the GROUP BY clause. (8120)
Here demo with error
Thanks an advance for any help or suggestion.