idiomatic · December 6, 2017 18:50
diff --git a/farmables.sql b/farmables.sql
 SELECT item.*,
    CAST(oclt.ChanceOrQuestChance AS DECIMAL(6, 2)) as Chance,
    COUNT(*) AS Spawns,
    oct.entry as Creature,
    oct.Name as `Creature Name`,
    CAST(AVG(oc.SpawnTimeSecs) AS DECIMAL(6, 0)) AS `Spawn Time`
 FROM (
  SELECT i.*, COUNT(*) AS `Creature Types`
  FROM (
    SELECT iclt.item AS Item,
        item_template.Name,
        CAST(SUM(iclt.ChanceOrQuestChance / ic.SpawnTimeSecs) AS DECIMAL(13, 6)) AS Availability
    FROM item_template
        JOIN creature_loot_template iclt ON iclt.item = item_template.entry
        JOIN creature_template ict ON ict.entry = iclt.entry
        JOIN creature ic ON ic.id = ict.entry
    WHERE ic.map < 2
        AND ic.SpawnTimeSecs > 0
        AND iclt.ChanceOrQuestChance > 0
    GROUP BY item_template.entry
  ) i
  JOIN creature_loot_template mclt ON mclt.item = i.Item
  GROUP BY i.Item
  ORDER BY i.Availability
 ) item
 JOIN creature_loot_template oclt ON oclt.item = item.Item
 JOIN creature_template oct ON oct.entry = oclt.entry
 JOIN creature oc ON oc.id = oct.entry
 WHERE oc.map < 2
    AND SpawnTimeSecs > 0
    AND ChanceOrQuestChance > 0
    AND `Creature Types` < 5
 GROUP BY item.Item, Name, Availability, oct.entry
 HAVING Chance > 0.1
    AND Spawns > 0
    AND Availability < 1.0
 ORDER BY Availability, Name, oct.entry
 LIMIT 10000;
diff --git a/result.md b/result.md
	SELECT item.*,
	CAST(oclt.ChanceOrQuestChance AS DECIMAL(6, 2)) as Chance,
	COUNT(*) AS Spawns,
	oct.entry as Creature,
	oct.Name as `Creature Name`,
	CAST(AVG(oc.SpawnTimeSecs) AS DECIMAL(6, 0)) AS `Spawn Time`
	FROM (
	SELECT i., COUNT() AS `Creature Types`
	FROM (
	SELECT iclt.item AS Item,
	item_template.Name,
	CAST(SUM(iclt.ChanceOrQuestChance / ic.SpawnTimeSecs) AS DECIMAL(13, 6)) AS Availability
	FROM item_template
	JOIN creature_loot_template iclt ON iclt.item = item_template.entry
	JOIN creature_template ict ON ict.entry = iclt.entry
	JOIN creature ic ON ic.id = ict.entry
	WHERE ic.map < 2
	AND ic.SpawnTimeSecs > 0
	AND iclt.ChanceOrQuestChance > 0
	GROUP BY item_template.entry
	) i
	JOIN creature_loot_template mclt ON mclt.item = i.Item
	GROUP BY i.Item
	ORDER BY i.Availability
	) item
	JOIN creature_loot_template oclt ON oclt.item = item.Item
	JOIN creature_template oct ON oct.entry = oclt.entry
	JOIN creature oc ON oc.id = oct.entry
	WHERE oc.map < 2
	AND SpawnTimeSecs > 0
	AND ChanceOrQuestChance > 0
	AND `Creature Types` < 5
	GROUP BY item.Item, Name, Availability, oct.entry
	HAVING Chance > 0.1
	AND Spawns > 0
	AND Availability < 1.0
	ORDER BY Availability, Name, oct.entry
	LIMIT 10000;