PURPOSE: List all Proving Grounds spawn group entries with a combined chance of less or greater than 100%,
NOTE:
https://mariadb.com/kb/en/library/aggregate-functions/
Code:
SELECT -- SELECT indicates these are the fields we want
s2.zone AS zone_sn, -- zone shortname from spawn2 (aliased)
s2.spawngroupID AS spawngroup_id, -- spawngroupID field from spawn2 table (aliased)
SUM(se.chance) AS spawn_chance -- sum of all chance field from spawnentry table (aliased)
FROM -- FROM lists tables to look for fields in
spawn2 AS s2, -- s2 is now shorthand for spawn2 table
spawnentry AS se, -- se is now shorthand for spawnentry table
npc_types AS nt -- nt is now shorthand for npc_types table
WHERE -- WHERE is how we filter results
( -- group similar conditions in parentheses
zone LIKE "chambers%" OR -- zone short name matches chambers* OR
zone = 'provinggrounds' -- zone short name is 'provinggrounds'
) AND -- end of grouping
se.spawngroupID = s2.spawngroupID AND -- match spawngroup_id in spawnentry and spawn2
se.npcID = nt.id AND -- match npc_id in spawnentry and npc_types
s2.enabled = 1 -- spawn2 entry enabled
GROUP BY -- indicates we're looking at aggregate data
spawngroup_id -- the field we're calculating via SUM in SELECT
HAVING -- HAVING is used for filtering aggregate functions
spawn_chance <> 100 -- data filter (anything not 100%)
ORDER BY -- sort by these columns
zone_sn, -- in order listed
spawngroup_id; -- so it's easier to make sense of the data returned
Code:
+----------------+---------------+--------------+
| zone_sn | spawngroup_id | spawn_chance |
+----------------+---------------+--------------+
| chambersa | 259467 | 200 |
| chamberse | 259477 | 200 |
| chambersf | 259482 | 200 |
| provinggrounds | 43688 | 200 |
+----------------+---------------+--------------+
4 rows in set (0.01 sec)