0

I have to give a count of the number of rooms in the Sparrow wing. I have a table for WING and a table for BEDROOMS. The bedroom table has wing_id as a foreign key and I'm having trouble joining the tables.

This is the query I have tried so far-

SELECT BEDROOM_ID, COUNT(*) FROM BEDROOMS WHERE WING_ID = ( SELECT WING_NAME FROM WING WHERE WING_NAME='SPARROW' ) GROUP BY WING_NAME;

getting an error - not a GROUP BY expression. Does anybody know how I can make this work?

Improve this question
1
  • 2
    I removed the three database tags, replacing them with the more generic "sql". Please tag the question with the database you are actually using. Commented Mar 23, 2015 at 18:16

4 Answers 4

Reset to default
2

This should give you the number of bedrooms in the SPARROW wing:

SELECT COUNT(*) FROM BEDROOMS B, WING W WHERE W.WING_NAME = 'SPARROW' AND B.WING_ID = W.WING_ID;

From what you're asking, you don't seem to need a group by. The reason, however, you're getting the error, is you're trying to select "BEDROOM_ID", however, BEDROOM_ID isn't in your GROUP BY clause.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

If you are going to demonstrate a join you should at least use ANSI-92 style joins.
Awesome this is great, but is there anyway I can show the wing name as well as the count?
@Ditto: the error is because there is something in the select list that's not in the group by clause, not the other way around... (i guess you already know this and just got confused for a second, but someone will be reading this answer somewhere along the line)
@ZoharPeled no I wasn't confused. I was stating that this answer will work but is using join syntax that was deprecated over 20 years ago.
@SeanLange: Sorry, I'm the one that got confused, i was aiming my comment to Ditto... there, fixed it now, sorry...
|
1

You can't use BEDROOM_ID in the SELECT clause if it's not in the GROUP BY clause. further more, This query will not work since you are comparing WING_ID with WING NAME. Also, I don't think you even need to use GROUP BY for this count.

SELECT 'SPARROW', COUNT(BEDROOM_ID) FROM BEDROOMS INNER JOIN WING ON(BEDROOMS.WING_ID = WING.WING_ID) WHERE WING_NAME='SPARROW'
Improve this answer

3 Comments

Thanks! how do i show the wing name as well as the room count though?
You already know the wing name, i'ts 'SPARROW'. there is no need to select it from the database,
Yes but I need the result to show the wing name and then the bedroom count.
0

Before writing a query, think about what you want to select and show. You are telling us that you want to count bedrooms in the Sparrow wing. So why do you select bedroom_id in your query? Which bedroom ID would you show of all those bedrooms in the wing?

Then you select from bedrooms where the wing ID is the one in question. But in the subquery you don't select the wing ID, but the wing name (which is 'SPARROW' of course). I suppose that is just a typo, right?

Then you group by wing_name. Thus you get one record per wing name. But you are only selecting records for one wing (SPARROW), so the GROUP BY clause doesn't do anything helpful. Moreover you are using it in the bedroom query, but the bedrooms table doesn't have a column wing_name, so you cannot group by it anyhow.

Here is your query cleaned up:

select count(*) from bedrooms where wing_id = ( select wing_id from wing where wing_name = 'SPARROW' );

You can also select min(bedroom_id), max(bedroom_id), the literal 'SPARROW', etc.

If you want to select columns from table wing, too, then select from wing and use a sub-query on bedrooms:

select wing_name, wing_size, (select count(*) from bedrooms b where b.wing_id = w.wing_id) as rooms from wing w where wing_name = 'SPARROW';
Improve this answer

Comments

0

Try This:

SELECT W.WING_NAME AS WING,COUNT(B.WING_ID) AS BED FROM WING W JOIN BEDROOM B ON W.WING_ID=B.WING_ID GROUP BY B.WING_ID;
Improve this answer

Comments