GroupBy

The GroupBy statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GroupBy statement is often used with aggregate functions ($COUNT(), $MAX(), $MIN(), $SUM(), $AVG()) to group the result-set by one or more columns.

The general format for GroupBy statement is as follows:

{
    "GroupBy": {
        "Fields": [
            "{FieldKey1}", 
            "{FieldKey2}"
        ]
    }
}

Here, {FieldKey} is the name of the object field, such as "LastName" or "CompanyName".

Examples

$COUNT - Return a number of AbEntries grouped by type

Use the AbEntryRead method to search for Address Book entries in the database.

// POST https://api.maximizer.com/octopus/Read
// Authorization: Bearer <token>
{
    "AbEntry": {
        "Scope": {
            "Fields": {
                "Type": 1,
                "$COUNT(type)": 1
            }
        },
        "GroupBy": {
            "Fields": [
                "type"
            ]
        }
    },
    "Configuration": {
        "Drivers": {
            "IAbEntrySearcher": "Maximizer.Model.Access.Sql.AbEntrySearcher"
        }
    }
}

$COUNT - Return an ordered number of AbEntries grouped by type

Use the AbEntryRead method to search for Address Book entries in the database.

// POST https://api.maximizer.com/octopus/Read
// Authorization: Bearer <token>
{
    "AbEntry": {
        "Scope": {
            "Fields": {
                "Type": 1,
                "$COUNT(type)": 1
            }
        },
        "GroupBy": {
            "Fields": [
                "type"
            ]
        },
        "OrderBy": {
            "Fields": [
                {
                    "$COUNT(type)": "DESC"
                }
            ]
        }
    },
    "Configuration": {
        "Drivers": {
            "IAbEntrySearcher": "Maximizer.Model.Access.Sql.AbEntrySearcher"
        }
    }
}

$MIN - Corporate revenue for Won and Lost opportunities Copy

Use the AbEntryRead method to search for Address Book entries in the database.

// POST https://api.maximizer.com/octopus/Read
// Authorization: Bearer <token>
{
    "Opportunity": {
        "Scope": {
            "Fields": {
                "Status/DisplayValue": 1,
                "$MIN(CorporateRevenue)": 1,
                "$COUNT()": 1
            }
        },
        "Criteria": {
            "SearchQuery": {
                "Status": {
                    "$IN": [
                        3,
                        4
                    ]
                }
            }
        },
        "GroupBy": {
            "Fields": [
                "Status"
            ]
        }
    },
    "Configuration": {
        "Drivers": {
            "IOpportunitySearcher": "Maximizer.Model.Access.Sql.OpportunitySearcher"
        }
    }
}

$MAX - Corporate revenue for Won and Lost opportunities

Use the AbEntryRead method to search for Address Book entries in the database.

// POST https://api.maximizer.com/octopus/Read
// Authorization: Bearer <token>
{
    "Opportunity": {
        "Scope": {
            "Fields": {
                "Status/DisplayValue": 1,
                "$MAX(CorporateRevenue)": 1,
                "$COUNT()": 1
            }
        },
        "Criteria": {
            "SearchQuery": {
                "Status": {
                    "$IN": [
                        3,
                        4
                    ]
                }
            }
        },
        "GroupBy": {
            "Fields": [
                "Status"
            ]
        }
    },
    "Configuration": {
        "Drivers": {
            "IOpportunitySearcher": "Maximizer.Model.Access.Sql.OpportunitySearcher"
        }
    }
}

$AVG - Corporate revenue for Won and Lost opportunities

Use the AbEntryRead method to search for Address Book entries in the database.

// POST https://api.maximizer.com/octopus/Read
// Authorization: Bearer <token>
{
    "Opportunity": {
        "Scope": {
            "Fields": {
                "Status/DisplayValue": 1,
                "$AVG(CorporateRevenue)": 1,
                "$COUNT()": 1
            }
        },
        "Criteria": {
            "SearchQuery": {
                "Status": {
                    "$IN": [
                        3,
                        4
                    ]
                }
            }
        },
        "GroupBy": {
            "Fields": [
                "Status"
            ]
        }
    },
    "Configuration": {
        "Drivers": {
            "IOpportunitySearcher": "Maximizer.Model.Access.Sql.OpportunitySearcher"
        }
    }
}

$SUM - Corporate revenue for Won and Lost opportunities

Use the AbEntryRead method to search for Address Book entries in the database.

// POST https://api.maximizer.com/octopus/Read
// Authorization: Bearer <token>
{
    "Opportunity": {
        "Scope": {
            "Fields": {
                "Status/DisplayValue": 1,
                "$SUM(CorporateRevenue)/DisplayValue": 1,
                "$COUNT()": 1
            }
        },
        "Criteria": {
            "SearchQuery": {
                "Status": {
                    "$IN": [
                        3,
                        4
                    ]
                }
            }
        },
        "GroupBy": {
            "Fields": [
                "Status"
            ]
        }
    },
    "Configuration": {
        "Drivers": {
            "IOpportunitySearcher": "Maximizer.Model.Access.Sql.OpportunitySearcher"
        }
    }
}