-1

I have 2 tables, the first table is is Apartments and it has ApartmentID and the other table is Rooms and has RoomsID, I'm trying to get the total amount of rooms per apartment but my issue is that, the total isn't a value of itself, the room table has a row for each room custom values, so if I run something like this SELECT * FROM Rooms I will get all data like this

ApartmentID = 1 , RoomID = 1 , WallPaint = Red etc...

ApartmentID = 1 , RoomID = 2 , WallPaint = blue etc...

As you can see, I can have several rooms that belongs to ApartmentID 1

What I want to achieve is, a query that returns for me the amount of Rooms for All Apartment ID, Like I'm trying to get such a response from server

ApartmentID 1 has 4 Rooms

ApartmentID 2 has 3 Rooms

This is just an example but ofcourse the response will be in JSON. I though about doing INNER JOIN query to get the data, but that only got me data I need for ApartmentID 1, how to I loop through all ApartmentID and for each ApartmentID I get the total Rooms in it keeping in mind that I need to count all rows for rooms and return the total as a value

Community
  • 1
  • 1
John
  • 141
  • 4
  • 12
  • 1
    [Why shouldn't I use mysql_* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?noredirect=1&lq=1). You want to `GROUP BY` – brombeer May 27 '20 at 10:30
  • 1
    where is your inner join attempt? – RST May 27 '20 at 10:30
  • 1
    You probably want something like `SELECT ApartmentID, COUNT(*) AS Roomcount FROM rooms GROUP BY ApartmentID`. (And in general, you should be working through a SQL tutorial at this point, that explains stuff like this.) – CBroe May 27 '20 at 10:31
  • 1
    @Dharman That is not even close to duplicate – Justinas May 27 '20 at 10:33
  • 1
    i don't understand why is this getting dowvoted – John May 27 '20 at 10:34
  • 1
    it's not type of homework, i started php literally yesterday and it's just something i want to pickup, i posted all the info i have, although i agree that i mentioned INNER JOIN solution but didn't post, because i tried it and already removed it since it didn't work so i didn't want to write it again. my fault on that part. – John May 27 '20 at 10:38
  • 1
    @Justinas I was under inpression that the problem is with broken PHP code which doesn't work because OP tried to use extension which was removed years ago from PHP. I will retract my vote and remove the misleading PHP code from the question. – Dharman May 27 '20 at 10:57

2 Answers2

1

Use COUNT and GROUP BY in query:

SELECT ApartmentID, COUNT(RoomId) as RoomsCount FROM Rooms GROUP BY ApartmentID

That will give you one apartment per row with total of rooms.

Justinas
  • 41,402
  • 5
  • 66
  • 96
0

Given that you have a table for Apartments, and Rooms, you could do something like so:

// Some example data: 
create table Apartments(id integer, name varchar(100));
insert into Apartments(id, name) values(1, "apt 1");
insert into Apartments(id, name) values(2, "apt 2");
insert into Apartments(id, name) values(3, "apt 3");


create table Rooms(id integer, name varchar(100), apt_id int);
insert into Rooms(id, name, apt_id) values(1, "room 1 in apt 1", 1);
insert into Rooms(id, name, apt_id) values(2, "room 2 in apt 1", 1);
insert into Rooms(id, name, apt_id) values(3, "room 3 in apt 2", 2);
insert into Rooms(id, name, apt_id) values(4, "room 4 in apt 1", 1);
insert into Rooms(id, name, apt_id) values(5, "room 5 in apt 2", 2);

// Query to fetch each apartment and count of rooms for each one.
SELECT a.name, 
  (SELECT count(apt_id) 
  FROM `Rooms` r 
  WHERE r.apt_id = a.id) count
FROM `Apartments` a;

// Results:
apt 1:  3
apt 2:  2
apt 3:  0
Dharman
  • 30,962
  • 25
  • 85
  • 135
Stuart
  • 6,630
  • 2
  • 24
  • 40
  • 1
    @John - I recreated the table structure only to create a query for you and test it -- just change my `apt_id` to your `ApartmentID` :) – Stuart May 27 '20 at 10:42
  • 1
    i don't understand your 'r' and 'a' what are they ? – John May 27 '20 at 10:44
  • 1
    They are just aliases, so you don't have to keep typing "Apartments", you can use the "a". It is the same as: `SELECT Apartments.name, (SELECT count(apt_id) FROM Rooms WHERE Rooms.apt_id = Apartments.id) count FROM Apartments;` – Stuart May 27 '20 at 10:45
  • yea i figured that, but they are causing me issues :( giving me unkown coloumns listed for 'a' – John May 27 '20 at 10:46
  • Make sure you use your actual IDentifiers :) – Stuart May 27 '20 at 10:47
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214730/discussion-between-john-and-stuart). – John May 27 '20 at 10:49