Challenging SQL query

Question

I have three tables linked between them following this scheme : A building have many statements which have many rooms. Each room has a type with a Enum field (Bathroom, Kitchen...) and an area.

Here is the SQL code :

CREATE TABLE `test_building` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`residence_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `residence_id` (`residence_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `test_settlement` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`building_id` int(10) unsigned NOT NULL DEFAULT '0',
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`residence_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `building_id` (`building_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `test_room` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`settlement_id` int(10) unsigned NOT NULL DEFAULT '0',
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`type` ENUM('Chambre', 'Cuisine', 'WC', 'Salon', 'Salle de bain') NOT NULL,
`area` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `settlement_id` (`settlement_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The goal is to store in a 2D php array the listing of every building and, for each building, the sum of the areas by type.

To give an exemple the final array should look like this :

Array(
    [Building 1] => array(
        [KitchensArea] => 52
        [BathrromsAreas] => 43
    )
    [Building 2] => array(
        [KitchensArea] => 69
        [BathrromsAreas] => 74
    )
)

Any ideas about how to do that ? I'm like totally stuck, I think it's possible with only one query but i don't really mind if it takes many.

Thanks in advance !

You can start with `select settlement_id,type,sum(area) from test_room group by settlement_id,type` and maybe join it to get the building. — eckes, Mar 27 '19 at 00:34
Join those 3 tables, then use `sum(case when..` https://stackoverflow.com/questions/19529864/mysql-using-sum-and-case — , Mar 27 '19 at 00:37

score 0 · Answer 1 · answered Mar 27 '19 at 00:29

0

Create two columns in your SQL called

Is_kitchen using case statement that returns 1 for kitchen and 0 otherwise.

Then is_bathroom with same idea.

Then grouping by building sum these two columns

answered Mar 27 '19 at 00:29

Saad Ahmad

393
1
7

score 0 · Accepted Answer · answered Mar 27 '19 at 12:40

The CASE WHEN statement proposed by catcon was the good thing to make. If anyone ever wonder here is the final query.

SELECT test_building.name, 
    SUM(CASE WHEN test_room.type = "Cuisine" THEN test_room.area ELSE 0 END) AS superficie_cuisine,
    SUM(CASE WHEN test_room.type = "Salle de bain" THEN test_room.area ELSE 0 END) AS superficie_salle_de_bain,
    SUM(CASE WHEN test_room.type = "Chambre" THEN test_room.area ELSE 0 END) AS superficie_chambre,
    SUM(CASE WHEN test_room.type = "WC" THEN test_room.area ELSE 0 END) AS superficie_wc,
    SUM(CASE WHEN test_room.type = "salon" THEN test_room.area ELSE 0 END) AS superficie_salon
    FROM test_building JOIN test_settlement ON test_settlement.building_id = test_building.id 
    JOIN test_room ON test_room.settlement_id = test_settlement.id 
    GROUP BY test_building.name

Thanks for your help !

Challenging SQL query

2 Answers2