CData Python Connector for Google Analytics

Build 20.0.7587

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.

for instance in session.query(func.count(Traffic.Id).label("CustomCount"), Traffic.Browser).group_by(Traffic.Browser):
	print("Count: ", instance.CustomCount)
	print("Browser: ", instance.Browser)
	print("---------")

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

for instance in session.execute(Traffic_table.select().with_only_columns([func.count(Traffic_table.c.Id).label("CustomCount"), Traffic_table.c.Browser])group_by(Traffic_table.c.Browser)):

SUM

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

for instance in session.query(func.sum(Traffic.AnnualRevenue).label("CustomSum"), Traffic.Browser).group_by(Traffic.Browser):
	print("Sum: ", instance.CustomSum)
	print("Browser: ", instance.Browser)
	print("---------")

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

for instance in session.execute(Traffic_table.select().with_only_columns([func.sum(Traffic_table.c.AnnualRevenue).label("CustomSum"), Traffic_table.c.Browser])group_by(Traffic_table.c.Browser)):

AVG

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

for instance in session.query(func.avg(Traffic.AnnualRevenue).label("CustomAvg"), Traffic.Browser).group_by(Traffic.Browser):
	print("Avg: ", instance.CustomAvg)
	print("Browser: ", instance.Browser)
	print("---------")

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

for instance in session.execute(Traffic_table.select().with_only_columns([func.avg(Traffic_table.c.AnnualRevenue).label("CustomAvg"), Traffic_table.c.Browser])group_by(Traffic_table.c.Browser)):

MAX and MIN

This example finds the maximum value and minimum value of a numeric column in a set of groups.

for instance in session.query(func.max(Traffic.AnnualRevenue).label("CustomMax"), func.min(Traffic.AnnualRevenue).label("CustomMin"), Traffic.Browser).group_by(Traffic.Browser):
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("Browser: ", instance.Browser)
	print("---------")

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

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

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587