подзапрос clausulas из Python

Question

Я пытаюсь преобразовать этот запрос из MySQL в Django, но я не знаю, как сделать вложенный выбор в python, пожалуйста, не могли бы вы мне помочь?

SELECT zet.zone, 
SUM(CASE WHEN zet.`status` = 'available' THEN zet.total ELSE 0 END) AS `ge_available`,
SUM(CASE WHEN zet.`status` = 'assigned' THEN zet.total ELSE 0 END) AS `ge_assigned`,
SUM(CASE WHEN zet.`status` = 'on_going' THEN zet.total ELSE 0 END) AS `ge_on_going`,
SUM(CASE WHEN zet.`status` = 'stopped' THEN zet.total ELSE 0 END) AS `ge_stopped`
FROM 
(SELECT vhgz.zone zone, vhgz.status status, COUNT(*) total
        FROM view_historic_group_zone vhgz, (SELECT group_id gid, MAX(date_change) fe
                                            FROM historical_group
                                            WHERE date_change <= '2020-09-21 15:12:56'
                                            GROUP BY group_id) hg
        WHERE vhgz.date = hg.Fe AND vhgz.gid = hg.gid
        GROUP BY vhgz.zone, vhgz.status) zet
GROUP BY zet.zone

Model.py

class viewhistoricgroupzone (models.Model):
    zone = models.CharField(max_length=50)
    date = models.DateTimeField()
    status = models.CharField(max_length=23, blank=True, null=True)
    gid = models.PositiveIntegerField()

Результат должен быть:

{"zone":"Spain","available":0,"assigned":1,"on_going":15,"stopped":2}
{"zone":"England","available":12,"assigned":4,"on_going":5,"stopped":1}
{"zone":"Italy","available":2,"assigned":4,"on_going":12,"stopped":4}
{"zone":"Germany","available":5,"assigned":8,"on_going":7,"stopped":3}