How to filter model with sum of fields and which is group_by with few fields?

10 views
Skip to first unread message

SHINTO PETER

unread,
Apr 9, 2015, 10:23:12 AM4/9/15
to django...@googlegroups.com
Model
class fcc_summary(models.Model):
    OCN_Id = models.AutoField( primary_key=True)
    OCN_Date = models.CharField(max_length = 20, blank = True, null = True)
    OCN_OCN_Id = models.CharField(max_length = 25, blank = True, null = True)
    OCN_StateCode = models.CharField(max_length = 5, blank = True, null = True)
    OCN_CallsAttempted = models.BigIntegerField(max_
length = 20, blank = True, null = True)
    OCN_Answered =   models.BigIntegerField(max_length = 20, blank = True, null = True)
    OCN_Busy = models.BigIntegerField(max_length = 20, blank = True, null = True)
    OCN_RingNoAnswer = models.BigIntegerField(max_length = 20, blank = True, null = True)
    OCN_UnassignedNo = models.BigIntegerField(max_length = 20, blank = True, null = True)
    OCN_CallType = models.IntegerField(max_length = 11, blank = True, null = True)
    OCN_IsRural = models.IntegerField(max_length = 11, blank = True, null = True)


to convert this queries into django using above models
1)
    SELECT `OCN_OCN_Id`,`OCN_StateCode`,SUM(`OCN_CallsAttempted`) AS OCN_CallsAttempted,
    SUM(`OCN_Answered`) AS OCN_Answered,SUM(`OCN_Busy`) AS OCN_Busy,
    SUM(`OCN_RingNoAnswer`) AS OCN_RingNoAnswer,SUM(`OCN_UnassignedNo`) AS `OCN_UnassignedNo`,`OCN_CallType`,`OCN_IsRural`
    FROM `fcc_report_fcc_summary`
    WHERE SUBSTRING(`OCN_Date`,1,7) = vReportDate AND `OCN_IsRural`=1 GROUP BY OCN_OCN_Id ,OCN_CallType ,OCN_StateCode

2)
    SELECT 'All','All' ,SUM(`OCN_CallsAttempted`) AS OCN_CallsAttempted,SUM(`OCN_Answered`) AS OCN_Answered,
    SUM(`OCN_Busy`) AS OCN_Busy,SUM(`OCN_RingNoAnswer`) AS OCN_RingNoAnswer,SUM(`OCN_UnassignedNo`) AS `OCN_UnassignedNo`,
    `OCN_CallType`,`OCN_IsRural`
    FROM `fcc_report_fcc_summary`  WHERE SUBSTRING(`OCN_Date`,1,7) = vReportDate AND `OCN_IsRural`=0
    GROUP BY OCN_OCN_Id ,OCN_CallType ,OCN_StateCode ORDER BY OCN_OCN_Id
Reply all
Reply to author
Forward
0 new messages