Gotta love PostgreSQL

questions_with_total_delegates.sql

-- questions + count of its delegated votes
SELECT
    questions_with_delegation_votes_count.*,
    CASE
      WHEN has_granter THEN count_by_setting_id
      ELSE 0
    END AS total_delegates
FROM (
  SELECT
    decidim_consultations_questions.*,
    COALESCE(CAST(granter_id > 0 AS BOOLEAN), FALSE) AS has_granter,
    COUNT(*) OVER (
      PARTITION BY decidim_action_delegator_settings.id
    ) AS count_by_setting_id
  FROM "decidim_consultations_questions"
  LEFT OUTER JOIN "decidim_consultations_votes"
    ON "decidim_consultations_votes"."decidim_consultation_question_id" = "decidim_consultations_questions"."id"
  LEFT JOIN "decidim_action_delegator_delegations"
    ON "decidim_consultations_votes"."decidim_author_id" = "decidim_action_delegator_delegations"."granter_id"
  LEFT JOIN "decidim_action_delegator_settings"
    ON "decidim_action_delegator_settings"."id" = "decidim_action_delegator_delegations"."decidim_action_delegator_setting_id"
  LEFT JOIN "decidim_consultations"
    ON "decidim_consultations"."id" = "decidim_action_delegator_settings"."decidim_consultation_id"
  WHERE "decidim_consultations_questions"."decidim_consultation_id" = 13
    AND "decidim_consultations_questions"."published_at" IS NOT NULL
  ORDER BY "decidim_consultations_questions"."order" ASC
) AS questions_with_delegation_votes_count
;