Why am I receiving an error about a column in the select list not being in an aggregate function or in the GROUP BY?

1

Hello there, I need your help.

This is a table [tbl_cco] . Here you can find a demo of what I explain next

Senza titolo
Senza titolo830×238 7.11 KB

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

Senza titolo
Senza titolo830×198 5.15 KB

But I can't get past this output

Senza titolo
Senza titolo830×103 3.24 KB

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.

2

Try changing:

FROM cte;

to:

FROM cte GROUP BY DateHour;

2 Likes
3 

;WITH cte AS (
    SELECT DISTINCT        
		[Total] AS Total, 
		[Total_X] AS Total_X,
		[Total_Y] AS Total_Y,
		[Total_Z] AS Total_Z, 
		[Cod],
		[DateHour] 
		FROM [dbo].[tbl_cco]
)
SELECT 		
        DateHour, 
		-- plrm
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_X ELSE 0 END) AS [T_X_P],
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_Y ELSE 0 END) AS [T_Y_P],
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_Z ELSE 0 END) AS [T_Z_P],	
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_X + Total_Y + Total_Z ELSE 0 END) AS [T_P],		
		
		-- ctna
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_X ELSE 0 END) AS [T_X_C],
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_Y ELSE 0 END) AS [T_Y_C],
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_Z ELSE 0 END) AS [T_Z_C],
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_X + Total_Y + Total_Z ELSE 0 END) AS [T_C],
		
		-- super totals
		SUM(Total_X) AS [T_X],
		SUM(Total_Y) AS [T_Y],
		SUM(Total_Z) AS [T_Z],		
		SUM(Total_X + Total_Y + Total_Z) AS [S_Total]	
				
FROM cte
WHERE Cod IN ('CTNA', 'PLRM')
GROUP BY DateHour 
ORDER BY DateHour
2 Likes
4

Btw, the index for the table must be clustered to be really useful for this query:

CREATE UNIQUE CLUSTERED INDEX [CodCo]
ON [dbo].[tbl_cco] (
[DateHour] ASC,
[Cod] ASC
);

1 Like
5

Thank you for help

1 Like