Create Table with Planet Type Counts in Each System

From EVEDev

Create a table containing counts of planet types in each system from data located in the mapDenormalize table. Note that this query takes some time and resources to run (45 seconds on my poor computer).

MySQL:

CREATE TABLE mapPlanetCount (
solarSystemID INT(11),
total TINYINT(4),
barren TINYINT(4),
gas TINYINT(4),
ice TINYINT(4),
lava TINYINT(4),
oceanic TINYINT(4),
plasma TINYINT(4),
shattered TINYINT(4),
storm TINYINT(4),
temperate TINYINT(4));
 
INSERT INTO mapPlanetCount
 
(SELECT 
planetsAll.solarSystemID AS solarSystemID,
IFNULL(planetsAll.total,0) AS total, 
IFNULL(planetsBarren.total,0) AS barren, 
IFNULL(planetsGas.total,0) AS gas, 
IFNULL(planetsIce.total,0) AS ice, 
IFNULL(planetsLava.total,0) AS lava, 
IFNULL(planetsOceanic.total,0) AS oceanic, 
IFNULL(planetsPlasma.total,0) AS plasma, 
IFNULL(planetsShattered.total,0) AS shattered, 
IFNULL(planetsStorm.total,0) AS storm, 
IFNULL(planetsTemperate.total,0) AS temperate
 
FROM 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(2016,13,12,2015,2014,2063,30889,2017,11)
GROUP BY mapDenormalize.solarSystemID)
AS planetsAll
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(2016)
GROUP BY mapDenormalize.solarSystemID)
AS planetsBarren ON planetsAll.solarSystemID = planetsBarren.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(13)
GROUP BY mapDenormalize.solarSystemID)
AS planetsGas ON planetsAll.solarSystemID = planetsGas.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(12)
GROUP BY mapDenormalize.solarSystemID)
AS planetsIce ON planetsAll.solarSystemID = planetsIce.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(2015)
GROUP BY mapDenormalize.solarSystemID)
AS planetsLava ON planetsAll.solarSystemID = planetsLava.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(2014)
GROUP BY mapDenormalize.solarSystemID)
AS planetsOceanic ON planetsAll.solarSystemID = planetsOceanic.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(2063)
GROUP BY mapDenormalize.solarSystemID)
AS planetsPlasma ON planetsAll.solarSystemID = planetsPlasma.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(30889)
GROUP BY mapDenormalize.solarSystemID)
AS planetsShattered ON planetsAll.solarSystemID = planetsShattered.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(2017)
GROUP BY mapDenormalize.solarSystemID)
AS planetsStorm ON planetsAll.solarSystemID = planetsStorm.solarSystemID
 
LEFT JOIN 
(SELECT mapDenormalize.solarSystemID, COUNT(mapDenormalize.solarSystemID) AS total
FROM mapDenormalize
WHERE mapDenormalize.typeID IN(11)
GROUP BY mapDenormalize.solarSystemID)
AS planetsTemperate ON planetsAll.solarSystemID = planetsTemperate.solarSystemID);