Query CASE ORDER BY ERROR

[Deleted]

Hi everyone,

I am trying to make a query that counts the amount of reserverd seatnumbers, grouped by 3 time units. This is the query so far:
Query:

SELECT SUM(SeatNum),
CASE
WHEN DATEPART(HOUR, StartDate) < 12 THEN 'Morning'
WHEN DATEPART(HOUR, StartDate) < 19 THEN 'Early night'
ELSE 'Late night'
END AS TimeUnit
FROM table1 Q LEFT JOIN table2 Y ON Q.table1= Y.table2
WHERE ReservedNum IS NOT NULL
GROUP BY
CASE
WHEN DATEPART(HOUR, StartDate) < 12 THEN 'Morning'
WHEN DATEPART(HOUR, StartDate) < 19 THEN 'Early night'
WHEN DATEPART(HOUR, StartDate) > 20 THEN 'Late night'
END

The problem I have is that I get this error message:
'Column 'Y.StartDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

However, If I do add StartDate into the ORDER BY, then the output is 7 lines (Because apparently there are 7 different hours in the database), instead of 3 lines (Evening, Early night and Late night).

How do I edit the query so that the outcome is like this:

SeatNum | TimeUnit

103400    | Morning

123410    | Early night

1199        | Late night

Thanks in advance!

[#2 · Accepted solution]

@Deleted 

Hi, Vera.

You're getting that error because the syntax in your first CASE statement (from the SELECT block) does not match the syntax of the CASE statement within your GROUP BY block.

Both CASE statements needs to be identical. Near enough is not good enough.

If you copy-and-paste your first CASE statement into the GROUP BY block (and remove the "AS TimeUnit" specification), your error will go away.

I don't understand your references to ORDER BY, but the way, as this is exclusively about the GROUP BY block (going off the example you provided).

As a side note, your CASE statement in the GROUP BY looks flawed in any case, as it does not handle 7pm and 8pm.

Here's a simple example - along with a reference table and data - based on your description showing how the syntax of the CASE statements is identical:

CREATE TABLE [dbo].[table1]
(
	[id] [int] PRIMARY KEY
	, [startDate] [datetime] NOT NULL
	, [reservedNum] [int] NOT NULL
)
GO

INSERT INTO
	[dbo].[table1]
VALUES
	(0, '2023-12-28 02:00:00', 5)
	, (1, '2023-12-28 03:00:00', 5)
	, (2, '2023-12-28 08:00:00', 5)
	, (3, '2023-12-28 14:00:00', 5)
	, (4, '2023-12-28 16:00:00', 5)
	, (5, '2023-12-28 20:00:00', 5)
	, (6, '2023-12-28 22:00:00', 5)
GO


SELECT
	SUM([t].[reservedNum]) AS [seatNum]
	, CASE
		WHEN DATEPART(hh, [t].[startDate]) < 12 THEN 'Morning'
		WHEN DATEPART(hh, [t].[startDate]) < 19 THEN 'Early night'
		ELSE 'Late night'
	END AS [timeUnit]
FROM
	[dbo].[table1] AS t
WHERE
	[t].[reservedNum] IS NOT NULL
GROUP BY
	CASE
		WHEN DATEPART(hh, [t].[startDate]) < 12 THEN 'Morning'
		WHEN DATEPART(hh, [t].[startDate]) < 19 THEN 'Early night'
		ELSE 'Late night'
	END;

Cheers,

Lain