Modify count()?

[lurtnowski]

I have

SELECT 
racks.room_id,racks.title,racks.rack_id,c.cnt 
FROM 
racks 
INNER JOIN 
(SELECT room_id,count(room_id) as cnt FROM racks GROUP By room_id) c 
ON 
racks.room_id = c.room_id 

which returns,

which is 7 results.
Is there a way to modify the query to return the number of racks per room as well as the title of each rack inside each room

[r937]

could you please explain what the subquery is counting?

is it counting racks per room?

if so why do you also want to see individual racks?

[lurtnowski]

oh, sorry,
I have this query, which returns the number of racks per room

SELECT room_id,count(room_id) AS cnt
FROM racks
GROUP BY room_id

Then, this one returns every rack_id, title, row, and bay of every rack

SELECT room_id,rack_id,title,row,bay 
FROM racks 
ORDER BY room_id

My goal here is to figure out how many racks are in each room and find out info about each rack so I can group them by room_id

[r937]

@lurtnowski

My goal here is to figure out how many racks are in each room and find out info about each rack so I can group them by room_id

first question – why not run two queries, because one’s an aggregate and one isn’t

second – what do you mean “group them”? do you mean sort them?

third – in your sample data, you have Racky, Rocky, Nathan… are these rack titles or room titles?

[lurtnowski]

Yes, it works if I just use 2 quieries.
I wanted the racks to be spit out in groups of rooms, I guess thats what the ORDER BY room_id clause did
those names of the racks, the rooms are only assigned by room_id which is 1-6)

[m_hutley]

@lurtnowski

I wanted the racks to be spit out in groups of rooms

Sounds like you’re wanting to GROUP_CONCAT the rack names; in which case your query is a single, aggregated, query on the rooms.

Change of query focus - or in english, a change of the subject of the sentence.
Your current query is “Tell me about the racks, but throw in the count of racks in it’s room on each row.”
What you’re suggesting sounds like “Tell me about the rooms, including the racks inside them.”