CData Python Connector for Google Data Catalog

Build 21.0.7930

Aggregate Functions

Certain aggregate functions can also be used within SQLAlchemy by using the func module. First, you will need to import it:

from sqlalchemy.sql import func

Once imported, the following aggregate functions are available:

COUNT

This example counts the number of records in a set of groups using the session object's query() method.

rs = session.query(func.count(Schemas.Id).label("CustomCount"), Schemas.Type).group_by(Schemas.Type)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Type: ", instance.Type)
	print("---------")

Alternatively, you can execute COUNT using the session object's execute() method.

rs = session.execute(Schemas_table.select().with_only_columns([func.count(Schemas_table.c.Id).label("CustomCount"), Schemas_table.c.Type])group_by(Schemas_table.c.Type))
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(Schemas.AnnualRevenue).label("CustomSum"), Schemas.Type).group_by(Schemas.Type)
for instance in rs:
	print("Sum: ", instance.CustomSum)
	print("Type: ", instance.Type)
	print("---------")

Alternatively, you can invoke SUM using the session object's execute() method.

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

AVG

This example calculates the average amount of a numeric column in a set of groups using the session object's query() method.

rs = session.query(func.avg(Schemas.AnnualRevenue).label("CustomAvg"), Schemas.Type).group_by(Schemas.Type)
for instance in rs:
	print("Avg: ", instance.CustomAvg)
	print("Type: ", instance.Type)
	print("---------")

Alternatively, you can invoke AVG using the session object's execute() method.

rs = session.execute(Schemas_table.select().with_only_columns([func.avg(Schemas_table.c.AnnualRevenue).label("CustomAvg"), Schemas_table.c.Type]).group_by(Schemas_table.c.Type))
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(Schemas.AnnualRevenue).label("CustomMax"), func.min(Schemas.AnnualRevenue).label("CustomMin"), Schemas.Type).group_by(Schemas.Type)
for instance in rs:
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("Type: ", instance.Type)
	print("---------")

Alternatively, you can invoke MAX and MIN using the session object's execute() method.

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

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930