I receive an error "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" when selecting record and counting those selected records.
I want to retrieve all the records of rooms included in a reservation and count all the rooms by:
Select Count(reservationId) as TotalRooms, RoomType From Reservations WHERE ReservationId = @ReservationId
I receive an error "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" when selecting record and counting those selected records.
I want to retrieve all the records of rooms included in a reservation and count all the rooms by:
Select Count(reservationId) as TotalRooms, RoomType From Reservations WHERE ReservationId = @ReservationId
Please help me why my query is incorrect. Thanks!
You need a group by clause. Since you didn't know this, I've heard good things about the book, Teach Yourself SQL in 10 Minutes.
--1st way to get total count based on roomtype
SELECT COUNT(reservationId) as TotalRooms,RoomType
FROM Reservations
WHERE ReservationId = @ReservationId
GROUP BY RoomType
--1st way to get total count based on reservation
SELECT DISTINCT COUNT(reservationId)OVER() as TotalRooms,RoomType
FROM Reservations
WHERE ReservationId = @ReservationId
mestizo
Member
106 Points
122 Posts
Error in Select Count
Nov 08, 2012 01:14 AM|LINK
hi,
I receive an error "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" when selecting record and counting those selected records.
I want to retrieve all the records of rooms included in a reservation and count all the rooms by:
Select Count(reservationId) as TotalRooms, RoomType From Reservations WHERE ReservationId = @ReservationId
Please help me why my query is incorrect. Thanks!
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Error in Select Count
Nov 08, 2012 01:20 AM|LINK
You need a group by clause. Since you didn't know this, I've heard good things about the book, Teach Yourself SQL in 10 Minutes.
mestizo
Member
106 Points
122 Posts
Re: Error in Select Count
Nov 08, 2012 01:27 AM|LINK
I did include the group by but I did not get the result i want.
when I do
Select Count(reservationId) as TotalRooms, RoomType From Reservations WHERE ReservationId = @ReservationId Group by reservationId
the query still incluides the error
"Reservations.RoomType is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
So when I do
Select Count(reservationId) as TotalRooms, RoomType From Reservations WHERE ReservationId = @ReservationId Group by RoomType
I do not get the result I want. I returns the number of rooms per roomtype
TotalRooms | RoomType
2 Small
5 Large
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Error in Select Count
Nov 08, 2012 04:09 AM|LINK
Try this:
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
yrb.yogi
Star
14460 Points
2402 Posts
Re: Error in Select Count
Nov 08, 2012 04:13 AM|LINK
.Net All About
jeeveshfulor...
Participant
1562 Points
287 Posts
Re: Error in Select Count
Nov 08, 2012 08:03 AM|LINK
use below code:-
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Error in Select Count
Nov 08, 2012 10:00 AM|LINK
And what is it that you actually want?