Database API for Server Script

I want to write a Python code to sum “allocated amount” from table " Payment Entry Reference" for the same “Sales Invoices” as below SQL

select SUM(allocated_amount) as paid from `tabPayment Entry Reference` where reference_name = 'ACC-SINV-2023-00063';

How to use Database API to get desired output which is 200.

I tried

a = frappe.db.get_list('Payment Entry Reference', filters={
    'reference_name': ['=', 'ACC-SINV-2023-00063']},
    fields=['SUM(allocated_amount) as paid']


Which is giving below Error

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from `tabPayment Entry Reference`\n\t\t\twhere `tabPayment Entry Reference`.`refe...' at line 2")

It’s working.

a = frappe.db.get_value('Payment Entry Reference', {'reference_name': 'ACC-SINV-2023-00063'}, ['SUM(allocated_amount) as paid'])


Ref : frappe.db.get_value

1 Like