Have Record Counts displayed with data

[lorirobn]

Hi,

I have a query that displays records off a major table. If there are 5 records for a given RoomID, the query will display 5 rows of information for the room. What I'd like to do now is just display 1 row, along with a column for the count. I have not worked much with counts and am unsure how to do this. Also, the query joins 4 tables to get this information, so it's a little more complicated.

My current SQL is:

SELECT tlkpSize.SizeID AS BedSize, tblRoomItems.RoomID
FROM tlkpItemSubcategory INNER JOIN (tlkpSize INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON tlkpSize.SizeID = tblItems.ItemSizeID) ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID
WHERE (((tlkpItemSubcategory.ItemSubcatID)="bed"));

The query results currently come out like this:
BedSize RoomID
T 115
T 115
T 115
T 115
T 115
T 123
T 123
T 123
T 123
T 123

Instead, I would like it to display:
BedSize RoomID Count
T 115 5
T 123 5

How would I add 'Count' to my Query?

Thank you in advance for any help.

 

[lespaul]

SELECT tlkpSize.SizeID AS BedSize, tblRoomItems.RoomID, Count(*)
FROM tlkpItemSubcategory INNER JOIN (tlkpSize INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON tlkpSize.SizeID = tblItems.ItemSizeID) ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID
WHERE (((tlkpItemSubcategory.ItemSubcatID)="bed"))
GROUP BY tlkpSize.SizeID AS BedSize, tblRoomItems.RoomID;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[mp9]

Try something like this:

SELECT tlkpSize.SizeID AS BedSize, tblRoomItems.RoomID, Count(tblRoomItems.RoomID) As RoomCount
FROM tlkpItemSubcategory INNER JOIN (tlkpSize INNER JOIN (tblItems INNER JOIN tblRoomItems ON tblItems.ItemID = tblRoomItems.ItemID) ON tlkpSize.SizeID = tblItems.ItemSizeID) ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID
WHERE tlkpItemSubcategory.ItemSubcatID="bed"
GROUP BY tlkpSize.SizeID, tblRoomItems.RoomID;

http://www22.brinkster.com/accessory/

 

[lespaul]

ooops....forgot to remove the alias from the GROUP BY clause!

Leslie

 

[lorirobn]

Excellent! Thank you kindly, lespaul and mp9. Works like a charm...didn't realize it was that easy.

Lori