SELECT
  accounts.username, SUM(account_template_service_type_addons.units + IFNULL(service_addons.units, 0))
FROM
  accounts
JOIN account_template_instances
  ON (account_template_instances.account_id = accounts.id)
JOIN account_template_service_types
  ON (account_template_service_types.account_template_id = account_template_instances.account_template_id)
JOIN account_template_service_type_addons
  ON (account_template_service_type_addons.account_template_service_type_id = account_template_service_types.id)
JOIN service_type_addons
  ON (account_template_service_type_addons.service_type_addon_id = service_type_addons.id)
JOIN services
  ON (account_template_service_types.service_type_id = services.service_type_id)
LEFT JOIN service_addons
  ON (service_addons.service_id = services.id)
WHERE
  account_template_service_types.service_type_id = 1
AND
  account_template_service_type_addons.service_type_addon_id = 1
AND
  services.account_template_instance_id = account_template_instances.id
AND
  services.service_type_id = 1
GROUP BY
  username