Count entitys dependent om field value and as entity reference

Question

I have two node types, Apartment and Building.

Apartment has two fields:
- "Building" a Entity Reference field that reference to the node type Building
- "Rooms" a Text List with values 1 room, 2 rooms, 3 rooms etc...

I want to have a View, that lists all Buildings and under each Building it displays how many Apartments WITH specific amount of rooms. Like this:

Building 1
1 x 1 room apartment
5 x 2 rooms apartment
6 x 3 rooms apartment

Building 2
7 x 1 room apartment
3 x 2 rooms apartment
2 x 3 rooms apartment

Is this possible to do with Views, and then HOW to do it?

Answer 1

In general Views Aggregator Plus and Views Calc can both generate tally reports like these.

Unfortuntaley by making rooms a text list you've basically made it (nearly) impossible for you to easily generate these types of reports. You're question isn't really a Views question, but 1 of how to write a tally report in SQL.

Since rooms is a simple string, its hard(er) to SUM the values to make your report. If the room was a reference to a room ID in another table then SQL, and Views and aggregation functions could more easily generate these reports.

Is this possible to do with Views, and then HOW to do it?

TLDR; Yes, it's possible. It's easier if you make your data into non-string values for aggregation. It's a futile effort to show you how because it's simply too messy in code to work with the data structures you've created.

EDIT

If these were numerical numbers for the number of rooms, or room IDs, you could easily use SUM aggregation to find the total. Then you would simply use a Views rewrite substition to show the output as something like the string [node-room-sum] x [node-room-size] room(s) aparament and use tokens derived from your View.