1

I have two models. One is Branch with branch_id and branch_name fields and Another is Sales with sales_amount and year field. Branch and Sales have a ForeignKey relationship. A Branch can have multiple Sales. What I'm trying to achieve is to group by with year where each year has its sales value. And get the dictionary of key(Branch name) and values(sales_list) If there are no sales for that year then there should be value '0'.

models.py

import datetime
from django.db import models


class Branch(models.Model):

    branch_id = models.IntegerField()
    name = models.CharField(max_length=50)


    def __str__(self):
        return self.name

class Sales(models.Model):
    branch = models.ForeignKey('Branch', on_delete = models.CASCADE)
    sales_amount = models.IntegerField()
    year = models.CharField(max_length=4) 

    class Meta:
        unique_together = (('year', 'branch'),)

    def __str__(self):
        return self.branch.name

views.py

from django.shortcuts import render
import json


from .models import Branch, Sales


def index(request):
    branches = Branch.objects.all()
    sales = Sales.objects.all()

    sales_list = []
    branch_list = []
    year_set = set()

    for sale in sales:
        year_set.add(sale.year)

    year_list = list(sorted(year_set))

    for branch in branches:
        sales_list.append(branch.sales_set.all().order_by('year'))
        branch_list.append(branch.name)

    sales_list = [[j.sales_amount for j in i] for i in sales_list]

    branch_dict = dict(zip(branch_list, sales_list))

    return render(request, 'index.html', {
        'year_list': year_list,
        'branch_dict': branch_dict
    })


I've

BRANCH001 with the year 2011 has 5000 sales, BRANCH001 with the year 2012 has 10000 sales, BRANCH002 with the year 2011 has 7000 sales

expected output = {'BRANCH001': [5000, 10000], 'BRANCH002': [0, 7000]}

actual output = {'BRANCH001': [5000, 10000], 'BRANCH002': [7000]}

Sammy J
  • 1,048
  • 10
  • 28
Roshan Maharjan
  • 162
  • 1
  • 11
  • If you want to create 0 values for missing data in the database (rather than in Python), you need a [calendar](https://stackoverflow.com/questions/24533485/aggregating-data-by-date-in-a-date-range-without-date-gaps-in-result-set?noredirect=1&lq=1) [table](https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql) in the database. – Endre Both Apr 05 '19 at 06:46

0 Answers0