CData Python Connector for Microsoft Dynamics 365 Business Central

Build 24.0.9060

Aggregate Functions

Certain aggregate functions can also be used within SQLAlchemy by using the func module.

To import this module, execute:

from sqlalchemy.sql import func

Once func is imported, the following aggregate functions are available:

COUNT

The following example counts the number of records in a set of groups using the session object's query() method.
rs = session.query(func.count(Accounts.AccountId).label("CustomCount"), Accounts.accountid).group_by(Accounts.accountid)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("accountid: ", instance.accountid)
	print("---------")

You can also execute COUNT using the session object's execute() method:

rs = session.execute(Accounts_table.select().with_only_columns([func.count(Accounts_table.c.AccountId).label("CustomCount"), Accounts_table.c.accountid])group_by(Accounts_table.c.accountid))
for instance in rs:

SUM

This example calculates the cumulative amount of a numeric column in a set of groups.

rs = session.query(func.sum(Accounts.AnnualRevenue).label("CustomSum"), Accounts.accountid).group_by(Accounts.accountid)
for instance in rs:
	print("Sum: ", instance.CustomSum)
	print("accountid: ", instance.accountid)
	print("---------")

You can also invoke SUM using the session object's execute() method.

rs = session.execute(Accounts_table.select().with_only_columns([func.sum(Accounts_table.c.AnnualRevenue).label("CustomSum"), Accounts_table.c.accountid]).group_by(Accounts_table.c.accountid))
for instance in rs:

AVG

This example uses the session object's query() method to calculate the average amount of a numeric column in a set of groups:
rs = session.query(func.avg(Accounts.AnnualRevenue).label("CustomAvg"), Accounts.accountid).group_by(Accounts.accountid)
for instance in rs:
	print("Avg: ", instance.CustomAvg)
	print("accountid: ", instance.accountid)
	print("---------")

You can also use the session object's execute() method to invoke AVG:

rs = session.execute(Accounts_table.select().with_only_columns([func.avg(Accounts_table.c.AnnualRevenue).label("CustomAvg"), Accounts_table.c.accountid]).group_by(Accounts_table.c.accountid))
for instance in rs:

MAX and MIN

This example finds the maximum value and minimum value of a numeric column in a set of groups.
rs = session.query(func.max(Accounts.AnnualRevenue).label("CustomMax"), func.min(Accounts.AnnualRevenue).label("CustomMin"), Accounts.accountid).group_by(Accounts.accountid)
for instance in rs:
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("accountid: ", instance.accountid)
	print("---------")

You can also use the session object's execute() method to invoke MAX and MIN:

rs = session.execute(Accounts_table.select().with_only_columns([func.max(Accounts_table.c.AnnualRevenue).label("CustomMax"), func.min(Accounts_table.c.AnnualRevenue).label("CustomMin"), Accounts_table.c.accountid]).group_by(Accounts_table.c.accountid))
for instance in rs:

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060